
package com.compuvest.web;

// <editor-fold defaultstate="collapsed" desc="import">
import java.io.ByteArrayInputStream;
import java.io.ObjectInputStream;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import javax.sql.DataSource;

import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;

import com.compuvest.webstatus.WebStat;
import javax.naming.NamingException;
// </editor-fold>

public class SQLBean implements java.io.Serializable {

// <editor-fold defaultstate="collapsed" desc="init variables">

    private final static Logger log = Logger.getLogger(SQLBean.class.getName());

    private final static int VENDOR_CVS = 2; // compuvest

    private DataSource ds;
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="SQLBean()">

    public SQLBean() {

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(NamingException e) {
            log.log(Level.SEVERE, "ds=null");
        }

        log.log(Level.INFO, "Init: mysql {0}", getVersion());
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVersion()">

    private String getVersion() {

        String version = " Error";

        try(Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("SELECT version()");
            ResultSet rs = us.executeQuery()
        ) {
            if(rs.next()) {
                version = rs.getString("version()");
            }
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getVersion()", e);
        }

        return " Version MySQL is: " + version;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getWord()">

/*
    public String getWord(String line) {

	if(line == null || line.length() < 2) return "";

	StringBuffer xml = new StringBuffer(200);

	Connection conn = null;
	try {
	    conn = ds.getConnection();

	    PreparedStatement us = conn.prepareStatement(
		"SELECT word FROM thesaurus1 WHERE word like '" + line + "%' AND title='T' GROUP BY word LIMIT 10");
//		"SELECT DISTINCT(word) word FROM thesaurus1 WHERE word like '" + line + "%' AND title='T' ORDER BY word LIMIT 10");
//	    us.setString(1, line);

	    xml.append("<response>");
	    ResultSet rs = us.executeQuery();
	    while(rs.next()) {
		xml.append("<opt>").append(rs.getString("word")).append("</opt>");
	    }
	    rs.close();
	    us.close();
	    xml.append("</response>");
	}
	catch(SQLException e) { System.out.println("Error SQLBean::getWord\n" + e.getMessage()); }
	finally {
	    if(conn != null) try { conn.close(); conn = null; } catch(SQLException ignore) { }
	}

//System.out.println("line=" + line + " [" + xml + "]");

	return xml.toString();
    }
*/
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getHistoryOrder()">

    static final String GET_ORDER_FROM_HISTORY =
	"SELECT order_cp " +
	"FROM sklad.orderHistory oh " +
	"LEFT JOIN sklad.orders rd USING(order_id) " +
	"WHERE oh.order_id=? AND rd.customer_id=?";

    public String getHistoryOrder(int orderID, int customerID) {

        String body = "";

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(GET_ORDER_FROM_HISTORY);
            us.setInt(1, orderID);
            us.setInt(2, customerID);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                body = rs.getString("order_cp");
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getHistoryOrder()", e);
        }

        return body;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getOrders()">

    static final String SELECT_GET_ORDERS =
        "SELECT order_id,orderNo,DATE_FORMAT(order_date,'%b %e, %Y %l%p') orderDate,shipping_price,tax_price," +
        "amount,cod,shipping_code,IF(LENGTH(order_cp) > 0,true,false) haveHistory " +
        "FROM sklad.orders " +
        "LEFT JOIN sklad.orderHistory USING(order_id) " +
        "WHERE customer_id=? " +
        "ORDER BY order_date DESC";
    static final String SELECT_GET_TRACKING =
	"SELECT trackingNumber trackingNo,DATE_FORMAT(shippedDate,'%b %e, %Y %l%p') shippDate " +
        "FROM sklad.tracking " +
        "WHERE order_id=? " +
        "ORDER BY shippedDate DESC";

    public ArrayList<Order> getOrders(int customerID) {

        ArrayList<Order> orderList = new ArrayList<>();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(SELECT_GET_ORDERS);
            us.setInt(1, customerID);
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
                Order order = new Order();

                order.setOrderID(rs.getInt("order_id"));
                order.setOrderNo(rs.getString("orderNo"));
                order.setOrderDate(rs.getString("orderDate"));
                order.setShippingCost(rs.getDouble("shipping_price"));
                order.setTaxCost(rs.getDouble("tax_price"));
                order.setGrandTotal(rs.getDouble("amount"));
                order.setCOD(rs.getString("cod"));
                order.setShippingDesc(rs.getString("shipping_code"));
                order.haveHistory(rs.getBoolean("haveHistory"));

                PreparedStatement us2 = conn.prepareStatement(SELECT_GET_TRACKING);
                us2.setInt(1, rs.getInt("order_id"));
                ResultSet rs2 = us2.executeQuery();
                while(rs2.next()) {
                    order.addTracking(new Tracking(rs2.getString("trackingNo"), rs2.getString("shippDate")));
                }
                rs2.close();
                us2.close();

                orderList.add(order);
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getOrders()", e);
        }

        return orderList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveCustomerLog()">

    public int saveCustomerLog(int advertID) {
/*
        try(Connection conn = ds.getConnection()) {

            // stat domain [ advertID, hitCount, orderCount, orderTotal ]
            CallableStatement cs = conn.prepareCall("{ call sklad.setDomainStat(?,1,0,0) }");
            cs.setInt(1, advertID);
            cs.executeUpdate();
            cs.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "saveCustomerLog()", e);
        }
*/
        return 0;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getTabsProducts()">
//
// its: [
//  id: item_id
//      it:{
//          id: it2.item_id
//          prc: it2.sell_price
//      }
//      bnr:{
//          sp: im.special
//          cl: im.closeout
//          bp: im.bestprice
//          cs: im.commingsoon
//      }
//      sku: it.accwareCatID
//      cnd: cs.description
//      prc: it.sell_price
//      qty: IF(it.stock='Y','Yes',it.stock_quantity)
//      pkg: ps.description
//      mnm: it.mfr_name
//      mfr: it.mfr_number
//      nm: it.name
// ]
//
	final static String TABS_PRODUCT =
	"SELECT " +
	"IFNULL(CONCAT('{its:['," +
	  "GROUP_CONCAT(" +
	   "CONCAT('{id:',it.item_id,',')," +
	   "IF(it2.item_id IS NULL, '', CONCAT('it:{id:',it2.item_id,',prc:',it2.sell_price,'},')),"+
	   "IF((im.special|im.closeout|im.commingsoon|im.bestprice)=0,'',CONCAT('bnr:{sp:',im.special,',cl:',im.closeout,',bp:',im.bestprice,',cs:',im.commingsoon,'},')),"+
	   "CONCAT('sku:',QUOTE(it.accwareCatID),',')," +
	   "CONCAT('cnd:',QUOTE(cs.description),',')," +
	   "CONCAT('prc:',it.sell_price,',')," +
	   "CONCAT('qty:',QUOTE(IF(it.stock='Y','Yes',it.stock_quantity)),',')," +
	   "CONCAT('pkg:',QUOTE(ps.description),',')," +
	   "CONCAT('mnm:',QUOTE(it.mfr_name),',')," +
	   "CONCAT('mfr:',QUOTE(it.mfr_number),',')," +
	   "CONCAT('nm:',QUOTE(it.name),'}')" +
	  ")," +
	"']}'),'') json " +
	"FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id " +
        "LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(it.accwareCatID,2),1)=ps.id " +
        "LEFT JOIN sklad.items it2 ON CONCAT(1,it.accwareCatID)=it2.accwareCatID " +
	"WHERE it.active='Y' AND it.mfr_number IN (";

	public String getTabsProducts(String mfrs) {

        String json = "";
        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(TABS_PRODUCT + mfrs + ") ORDER BY it.vendor_id ASC");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		json = rs.getString("json");
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getTabsProducts()", e);
        }

        return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItem()">

    final static String GET_ITEM =
        "SELECT HIGH_PRIORITY i.item_id itemID,i.category_id categoryID,i.name itemName,IFNULL(itd.json,'') json," +
        "i.sell_price sellPrice,i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2," +
        "i.res_price3 resPrice3,i.res_quantity3 resQuantity3,i.stock_quantity-i.reserv_quantity quantity," +
        "IF(i.stock='Y',true,false) stock,i.weight weight,RIGHT(i.accwareCatID,12) accwareCode," +
        "i.mfr_name mfrName,i.mfr_number mfrNumber,i.mfr_link mfrLink,cs.description conditions," +
        "ps.description package,i.package_include packageInclude,i.comments comments," +
        "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask,i.warrantyMask warrantyMask," +
        "i.warrantyPrice1 warrantyPrice1,i.warrantyPrice2 warrantyPrice2,i.warrantyPrice3 warrantyPrice3," +
        "CONCAT(" +
        "IF(IFNULL(p.pref2,'')='','',CONCAT('AND p.pref2=',QUOTE(p.pref2),' '))," +
        "IF(IFNULL(p.pref3,'')='','',CONCAT('AND p.pref3=',QUOTE(p.pref3),' '))," +
        "IF(IFNULL(p.pref4,'')='','',CONCAT('AND p.pref4=',QUOTE(p.pref4),' '))," +
        "IF(IFNULL(p.pref5,'')='','',CONCAT('AND p.pref5=',QUOTE(p.pref5),' '))" +
        ") search," +
	"c.name categoryName,c.parent parent,c.category_id subcatID,g.name subcatName " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsDesc itd USING(item_id) " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(i.accwareCatID,2),1)=ps.id " +
        "WHERE i.active='Y' AND ";

    public Items getItem(int itemID, String accwareCode) {

        long startEvent = System.currentTimeMillis();

        String expr = accwareCode == null ? "i.item_id=" + itemID : "i.accwareCatID='" + accwareCode + "'";

        Items items = new Items();

        int categoryID = 0;
        try(Connection conn = ds.getConnection()) {
            String search = "";

            PreparedStatement us = conn.prepareStatement(GET_ITEM + expr);
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                //System.out.println("itemID=" +itemID + ": " + rs.getString("json"));
                categoryID = rs.getInt("categoryID");

                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setCategoryID(categoryID);
                item.setItemName(rs.getString("itemName"));
                item.setJSONDescription(rs.getString("json"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setWeight(rs.getDouble("weight"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setMfrLink(rs.getString("mfrLink"));
                item.setCondition(rs.getString("conditions"));
                item.setPackage(rs.getString("package"));
                item.setPackageInclude(rs.getString("packageInclude"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setWarrantyMask(rs.getInt("warrantyMask"));
                item.setWarrantyPrice1(rs.getDouble("warrantyPrice1"));
                item.setWarrantyPrice2(rs.getDouble("warrantyPrice2"));
                item.setWarrantyPrice3(rs.getDouble("warrantyPrice3"));
                item.setComments(rs.getString("comments"));

		item.setCategoryName(rs.getString("categoryName"));
                item.setCategoryParent(rs.getInt("parent"));
                item.setSubcategoryName(rs.getString("subcatName"));

		items.setCategoryID(categoryID);
                items.setSubcategoryID(rs.getInt("subcatID"));

                items.setCategoryName(rs.getString("categoryName"));
                items.setSubcategoryName(rs.getString("subcatName"));

                search = rs.getString("search");

                items.addItem(item);
            }
            rs.close();
            us.close();

            StringBuilder json = new StringBuilder();
            json.append('[');
            us = conn.prepareStatement(
		"SELECT IFNULL(CONCAT('{id:',i.item_id,',name:',QUOTE(i.name),',price:',i.sell_price,',sku:',QUOTE(i.accwareCatID),'}'),'{}') json " +
		"FROM sklad.items i " +
                "LEFT JOIN sklad.category c USING(category_id) " +
                "LEFT JOIN sklad.preference p USING(item_id) " +
                "WHERE i.active='Y' " + search + " AND i.category_id=? AND i.item_id<>? LIMIT 10");
                us.setInt(1, categoryID);
                us.setInt(2, itemID);
            rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) json.append(',');
		json.append(rs.getString("json"));
            }
            rs.close();
            us.close();
            json.append(']');
            items.setRelativeItemJSON(json.toString());

        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItem()", e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemForBasket()">
/*
SELECT i.item_id,i.sell_price,i.stock_quantity,i.reserv_quantity
FROM sklad.items i
LEFT JOIN sklad.itemsAdvert ad USING(item_id)
LEFT JOIN sklad.category g USING(category_id)
LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id
LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(i.accwareCatID,2),1)=ps.id
WHERE i.active='Y' AND (i.stock_quantity - i.reserv_quantity) > 0 AND i.item_id=1591511;
 */
    final static String GET_ITEMS_FOR_BASKET =
        "SELECT i.item_id item_id,i.category_id category_id,g.name catName,i.name itemName," +
        "i.sell_price sell_price,i.cost_price cost_price,i.vendor_id vendor_id," +
        "i.res_price1 res_price1,i.res_price2 res_price2,i.res_quantity2 res_quantity2," +
        "i.res_price3 res_price3,i.res_quantity3 res_quantity3," +
        "(i.stock_quantity - i.reserv_quantity) quantity,IF(i.stock='Y',true,false) stock,i.weight weight," +
        "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfr_name,i.mfr_number mfr_number,cs.description conditions,ps.description package," +
        "i.warrantyMask warrantyMask,i.warrantyPrice1 warrantyPrice1,i.warrantyPrice2 warrantyPrice2," +
        "i.warrantyPrice3 warrantyPrice3,IFNULL(ad.upc,0) upc " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id) " +
        "LEFT JOIN sklad.category g USING(category_id) " + // only for categoryName
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(i.accwareCatID,2),1)=ps.id " +
        "WHERE i.active='Y' AND i.item_id=?";

    final static String GET_PROMO_PROCENT =
	"SELECT code,procent FROM sklad.itemsPromotionCode WHERE endDate >= CURRENT_DATE() AND item_id=?";

    public Item getItemForBasket(int itemID) {

        Item item = new Item();
        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(GET_ITEMS_FOR_BASKET);
            us.setInt(1, itemID);
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                item.setItemID(rs.getInt("item_id"));
                item.setCategoryID(rs.getInt("category_id"));
                item.setVendorID(rs.getInt("vendor_id"));
                item.setCategoryName(rs.getString("catName"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sell_price"));
                item.setCostPrice(rs.getDouble("cost_price"));
                item.setResPrice1(rs.getDouble("res_price1"));
                item.setResPrice2(rs.getDouble("res_price2"));
                item.setResQuantity2(rs.getInt("res_quantity2"));
                item.setResPrice3(rs.getDouble("res_price3"));
                item.setResQuantity3(rs.getInt("res_quantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setWeight(rs.getDouble("weight"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setUPC(rs.getString("upc"));
                item.setMfrName(rs.getString("mfr_name"));
                item.setMfrNumber(rs.getString("mfr_number"));
                item.setCondition(rs.getString("conditions"));
                item.setPackage(rs.getString("package"));
                item.setWarrantyMask(rs.getInt("warrantyMask"));
                item.setWarrantyPrice1(rs.getDouble("warrantyPrice1"));
                item.setWarrantyPrice2(rs.getDouble("warrantyPrice2"));
                item.setWarrantyPrice3(rs.getDouble("warrantyPrice3"));
            }
            rs.close();
            us.close();

            // add promotion code to item
            Map<String,Double> promocode = new HashMap<String,Double>();
            us = conn.prepareStatement(GET_PROMO_PROCENT);
            us.setInt(1, itemID);
            rs = us.executeQuery();
            while(rs.next()) {
                promocode.put(rs.getString("code"), rs.getDouble("procent"));
            }
            rs.close();
            us.close();
            item.setPromotionCode(promocode);
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItemForBasket()", e);
        }

        return item;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getPromoCodeForBasket()">

    final static String GET_PROMOCODE = "SELECT id,item_id,code,procent,price,count FROM sklad.itemsPromotionCode WHERE endDate >= CURRENT_DATE() AND code=?";
    final static String UPDATE_PROMOCODE = "UPDATE sklad.itemsPromotionCode SET count=count+1 WHERE id=?";

	public PromotionCode getPromoCodeForBasket(String custPromoCode) {

        PromotionCode promo = null;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(GET_PROMOCODE);
            us.setString(1, custPromoCode);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                promo = new PromotionCode(rs.getInt("id"), rs.getString("code"), rs.getDouble("procent"), rs.getDouble("price"), rs.getInt("count"));
            }
            rs.close();
            us.close();

            //if(promo != null) {
                // set status use
                //if(promo != null && promo.getPromoID() != 0) {
                if(promo != null) {
                    us = conn.prepareStatement(UPDATE_PROMOCODE);
                    us.setInt(1, promo.getPromoID());
                    us.executeUpdate();
                    us.close();
                }

                // not aviable if used before (else coupon in procent)
                //if(promo.getPromoPrice() == 0 && promo.getCount() != 0) {
                //    promo = null;
                //}
            //}
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getPromoCodeForBasket()", e);
        }

        return promo;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemDropPriceAlert()">

    final static String GET_ITEMS_DROP_PRICE_ALERT =
	"SELECT " +
	 "IFNULL(" +
	  "CONCAT(" +
	   "'{id:',it.item_id," +
	   "',sku:',QUOTE(it.accwareCatID)," +
	   "',price:',it.sell_price," +
	   "',name:',QUOTE(it.name),'}'" +
	  ")" +
	 ",'') json " +
        "FROM sklad.items it " +
        "WHERE it.item_id=?";

    public String getItemDropPriceAlert(String itemID) {

	String json = "";

        if(itemID == null) return json;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(GET_ITEMS_DROP_PRICE_ALERT);
            us.setString(1, itemID);
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		json = rs.getString("json");
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItemDropPriceAlert()", e);
        }

        return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getBrands()">

    public Items getBrands(int categoryID, int subcatID, int sortID, String brand, Map<Integer,String> popData, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        if(brand.length() == 0) return items;

        //sort
        String sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC";
        switch(sortID) {
            case  1 : sort = "i.sell_price ASC  "; break;
            case  2 : sort = "i.sell_price DESC "; break;
            case  3 : sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC"; break;
        }

        String catstr = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0 ? "" : " AND c.category_id=" + subcatID  + " ";

        // preference
        StringBuilder strSel = new StringBuilder();
        if(popData != null && popData.size() > 0) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' "); break;
                }
            }
        }

        Preference mfrName = new Preference();
        Preference conds = new Preference();
        Preference sr1 = new Preference();
        Preference sr2 = new Preference();
        Preference sr3 = new Preference();
        Preference sr4 = new Preference();
        Preference sr5 = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;

            // fill preference
            if(subcatID != 0) {
                us = conn.prepareStatement(
                    "SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
                    "FROM sklad.items i " +
                    "LEFT JOIN sklad.preference p USING(item_id) " +
                    "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
                    "WHERE i.active='Y' AND i.category_id=? " + strSel + " AND i.mfr_name='" + brand + "'"
                );
                us.setInt(1, subcatID);

                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    sr1.addContent(rs.getString("pref1"));
                    sr2.addContent(rs.getString("pref2"));
                    sr3.addContent(rs.getString("pref3"));
                    sr4.addContent(rs.getString("pref4"));
                    sr5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }

            // make category
            us = conn.prepareStatement(
                "SELECT count(*) itemsCount,g.category_id category_id,g.name name " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.category c USING(category_id) " +
                "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
                "WHERE i.active='Y' AND i.mfr_name='" + brand + "' AND g.category_id IS NOT NULL " +
                "GROUP BY g.category_id ORDER BY g.sort,g.name");

            rs = us.executeQuery();
            while(rs.next()) {
                items.addCategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
            }
            rs.close();
            us.close();

            // make subcategory
            if(categoryID != 0) {
                us = conn.prepareStatement(
                    "SELECT count(*) itemsCount,c.category_id category_id,c.name name " +
                    "FROM sklad.items i " +
                    "LEFT JOIN sklad.category c USING(category_id) " +
                    "LEFT JOIN sklad.preference p USING(item_id) " +
                    "WHERE i.active='Y' AND i.mfr_name='" + brand + "' AND c.parent=? " +
                    "GROUP BY c.category_id ORDER BY c.sort,c.name"
                );
                us.setInt(1, categoryID);

                rs = us.executeQuery();
                while(rs.next()) {
                    items.addSubcategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
                }
                rs.close();
                us.close();
            }

            // items
            us = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.sell_price sellPrice," +
                "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
                "i.res_quantity3 resQuantity3," +
                "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock," +
                "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber," +

                "IFNULL(i2.item_id,0) itemIDR,IFNULL(i2.stock_quantity-i2.reserv_quantity,0) quantityR," +
                "IF(i2.stock='Y',true,false) stockR,IFNULL(i2.sell_price,0.0) sellPriceR," +

                "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +

                "cs.description conditions " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.category c USING(category_id) " +
                "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
                "LEFT JOIN sklad.items i2 ON CONCAT(1,i.accwareCatID)=i2.accwareCatID " +
                "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
                "WHERE i.active='Y' AND i.mfr_name='" + brand + "' " + strSel + catstr + scatstr +
                " ORDER BY " + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);

            rs = us.executeQuery();
            for(int i = 0; rs.next(); i++) {
                if(subcatID != 0 && i == 0) {
                    mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                    conds.setTitle("Condition"); items.addPreference(conds);
                    sr1.setTitle(rs.getString("sn1")); items.addPreference(sr1);
                    sr2.setTitle(rs.getString("sn2")); items.addPreference(sr2);
                    sr3.setTitle(rs.getString("sn3")); items.addPreference(sr3);
                    sr4.setTitle(rs.getString("sn4")); items.addPreference(sr4);
                    sr5.setTitle(rs.getString("sn5")); items.addPreference(sr5);
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setCondition(rs.getString("conditions"));

                item.setItemIDR(rs.getInt("itemIDR"));
                item.setQuantityR(rs.getInt("quantityR"));
                item.setStockR(rs.getBoolean("stockR"));
                item.setPriceR(rs.getDouble("sellPriceR"));

                items.addItem(item);
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getBrands()", e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="array_range()">
// find include value between min and max in array
// return max in range or 0 if not find range

    public int array_range(Integer[] array, int min, int max) {

        int high = array.length, low = -1, probe, data;

        while(high - low > 1) {
            probe = (high + low) >>> 1;

            data = array[probe];
            if((data >= min) && (data <= max))
                return data;

            if(data < min)
                low = probe;
            else
                high = probe;
        }
        return 0;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItems()">
/*
 * NEED change for mysql 5.1 this block not work
 *
SELECT i.item_id itemID,i.category_id categoryID,i.name itemName,i.sell_price sellPrice,
i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3,
i.res_quantity3 resQuantity3,i.stock_quantity-i.reserv_quantity quantity,
IF(i.stock='Y',true,false) stock,RIGHT(i.accwareCatID,12) accwareCode,
(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask,
cs.description conditions,c.parent parent,

IFNULL(i2.item_id,0) itemIDR,IFNULL(i2.stock_quantity-i2.reserv_quantity,0) quantityR,
IF(i2.stock='Y',true,false) stockR,IFNULL(i2.sell_price,0) sellPriceR,

c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5
FROM sklad.items i LEFT JOIN sklad.itemsMask im USING(item_id) LEFT JOIN sklad.category c USING(category_id)
LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id
LEFT JOIN sklad.preference p ON i.item_id=p.item_id LEFT JOIN sklad.items i2 ON CONCAT(1,i.accwareCatID)=i2.accwareCatID
WHERE i.active='Y' AND i.category_id=512
ORDER BY i.sell_price ASC LIMIT 0,15;

SELECT i.item_id itemID,i.category_id categoryID,i.name itemName,i.sell_price sellPrice,
i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3,
i.res_quantity3 resQuantity3,i.stock_quantity - i.reserv_quantity quantity,
IF(i.stock='Y',true,false) stock,RIGHT(i.accwareCatID,12) accwareCode,
(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask,
c.parent parent

FROM sklad.items i LEFT JOIN sklad.itemsMask im USING(item_id) LEFT JOIN sklad.category c ON c.category_id=i.category_id
LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id
WHERE i.active='Y' AND i.category_id=512
ORDER BY i.sell_price ASC LIMIT 0,15;
 */
    public Items getItems(int subcatID, int sortID, Map<Integer,String> popData, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        // preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case 0:
                        Pattern pattern = Pattern.compile("\\$([\\d]{1,7}).+\\$([\\d]{1,7})"); // str= $867 - $967
                        Matcher m = pattern.matcher(popData.get(key));
                        if(m.find()) {
                            int minPrice = getInt(m.group(1), 0), maxPrice = getInt(m.group(2), 0);

                            if(minPrice < maxPrice) {
                                strSel.append(" AND i.sell_price>=").append(minPrice).append(" AND i.sell_price<=").append(maxPrice + 1).append(" ");
                            }
                        }
                        break;
                    case 1:  strSel.append("AND i.mfr_name='").append(popData.get(key)).append("' "); break;
                    case 2:  strSel.append("AND cs.description='").append(popData.get(key)).append("' "); break;
                    default: strSel.append("AND p.pref").append(key-2).append("='").append(popData.get(key)).append("' "); break;
                }
            }
        }

        //sort
        String sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC";
        switch(sortID) {
            case  1 : sort = "i.sell_price ASC  "; break;
            case  2 : sort = "i.sell_price DESC "; break;
            case  3 : sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC"; break;
        }

        Preference priceRange = new Preference();
        Preference mfrName = new Preference();
        Preference conds = new Preference();
        Preference sr1 = new Preference();
        Preference sr2 = new Preference();
        Preference sr3 = new Preference();
        Preference sr4 = new Preference();
        Preference sr5 = new Preference();
        TreeSet<Integer> prices = new TreeSet<>(); // for range price

        try(Connection conn = ds.getConnection()) {

            // items count and fill preference
            PreparedStatement us = conn.prepareStatement(
                "SELECT i.mfr_name mfr_name,cs.description conds,i.sell_price sellPrice," +
                "p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.preference p USING(item_id) " +
                "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
                "WHERE i.active='Y' AND i.category_id=? " + strSel +
                " ORDER BY i.sell_price");
            us.setInt(1, subcatID);

            int itemCount = 0;
            ResultSet rs = us.executeQuery();
            for(; rs.next(); itemCount++) {
                prices.add(rs.getInt("sellPrice"));
                mfrName.addContent(rs.getString("mfr_name"));
                conds.addContent(rs.getString("conds"));
                sr1.addContent(rs.getString("pref1"));
                sr2.addContent(rs.getString("pref2"));
                sr3.addContent(rs.getString("pref3"));
                sr4.addContent(rs.getString("pref4"));
                sr5.addContent(rs.getString("pref5"));
            }
            rs.close();
            us.close();

            if(itemCount == 0) return items;

    	    int minSellPrice = prices.first();
            int maxSellPrice = prices.last();
            items.setItemsCount(itemCount);

            // make price range
            if(itemCount < Items.LINE_ON_PAGE) {
                priceRange.addContent(String.format("$%d - $%d", minSellPrice, maxSellPrice));
            }
            else {
                int chanckPrice = maxSellPrice / 5;
                Integer[] array = (Integer[])prices.toArray(new Integer[prices.size()]);

                int endPrice = array_range(array, minSellPrice, chanckPrice);
                if(endPrice != 0)
                    priceRange.addContent(String.format("$%d - $%d", minSellPrice, endPrice));

                endPrice = array_range(array, chanckPrice, chanckPrice * 2);
                if(endPrice != 0)
                    priceRange.addContent(String.format("$%d - $%d", chanckPrice, endPrice));

                endPrice = array_range(array, chanckPrice * 2, chanckPrice * 3);
                if(endPrice != 0)
                    priceRange.addContent(String.format("$%d - $%d", chanckPrice * 2, endPrice));

                endPrice = array_range(array, chanckPrice * 3, chanckPrice * 4);
                if(endPrice != 0)
                    priceRange.addContent(String.format("$%d - $%d", chanckPrice * 3, endPrice));

                endPrice = array_range(array, chanckPrice * 4, maxSellPrice);
                if(endPrice != 0)
                    priceRange.addContent(String.format("$%d - $%d", chanckPrice * 4, maxSellPrice));
            }

            us = conn.prepareStatement(
                "SELECT i.item_id itemID,i.category_id categoryID,i.name itemName,i.sell_price sellPrice," +
                "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
                "i.res_quantity3 resQuantity3,i.stock_quantity-i.reserv_quantity quantity," +
                "IF(i.stock='Y',true,false) stock,i.accwareCatID accwareCode," +
                "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask," +
                "cs.description conditions,c.parent parent,LEFT(RIGHT(i.accwareCatID,2),1) condid," + // 330006061-[3]2 -> 3

                //"IFNULL(i2.item_id,0) itemIDR,IFNULL(i2.stock_quantity-i2.reserv_quantity,0) quantityR," +
                //"IF(i2.stock='Y',true,false) stockR,IFNULL(i2.sell_price,0) sellPriceR," +

                //"i.relv_price relvPrice,i.relv_stock relvStock,i.relv_quantity relvQuantity," +

                "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5 " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.itemsMask im USING(item_id) " +
                "LEFT JOIN sklad.category c USING(category_id) " +
                "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
                "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
                //"LEFT JOIN sklad.items i2 ON CONCAT(1,i.accwareCatID)=i2.accwareCatID " +
                "WHERE i.active='Y' AND i.category_id=? " + strSel +
                " ORDER BY " + sort +
                " LIMIT ?,?");
            us.setInt(1, subcatID);
            us.setInt(2, pageNo * Items.LINE_ON_PAGE);
            us.setInt(3, Items.LINE_ON_PAGE);

            rs = us.executeQuery();
            for(int i = 0; rs.next(); i++) {
                if(i == 0) {
                    priceRange.setTitle("Price range"); items.addPreference(priceRange);
                    mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                    conds.setTitle("Condition"); items.addPreference(conds);
                    sr1.setTitle(rs.getString("sn1")); items.addPreference(sr1);
                    sr2.setTitle(rs.getString("sn2")); items.addPreference(sr2);
                    sr3.setTitle(rs.getString("sn3")); items.addPreference(sr3);
                    sr4.setTitle(rs.getString("sn4")); items.addPreference(sr4);
                    sr5.setTitle(rs.getString("sn5")); items.addPreference(sr5);
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setCategoryID(rs.getInt("categoryID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setConditionId(rs.getInt("condid"));
                item.setCondition(rs.getString("conditions"));
                item.setCategoryParent(rs.getInt("parent"));

                //item.setItemIDR(rs.getInt("itemIDR"));
                //item.setQuantityR(rs.getInt("quantityR"));
                //item.setStockR(rs.getBoolean("stockR"));
                //item.setPriceR(rs.getDouble("sellPriceR"));

                // second items (relative)
                //item.setRelativePrice(rs.getDouble("relvPrice"));
                //item.setRelativeStock(rs.getBoolean("relvStock"));
                //item.setRelativeQuantity(rs.getInt("relvQuantity"));

                items.addItem(item);
            }
            rs.close();
            us.close();

        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItems()", e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsPage()">
/*
explain
SELECT SQL_CALC_FOUND_ROWS CONCAT('{',
'id:',it.item_id,
IF(it2.item_id,CONCAT(',additem:[{id:',it2.item_id,',price:',it2.sell_price,'}]'),''),
',sku:',QUOTE(it.accwareCatID),
IF(im.special|im.closeout|im.commingsoon|im.bestprice,CONCAT(',banner:[{special:',im.special,',clearance:',im.closeout,',bestprice:',im.bestprice,',comesoon:',im.commingsoon,'}]'),''),
',cond:',QUOTE(cs.description),
',price:',it.sell_price,
',stock:',QUOTE(IF(it.stock='Y','Yes',it.stock_quantity)),
',packg:',QUOTE(ps.description),
',mfrName:',QUOTE(it.mfr_name),
',mfrNumber:',QUOTE(it.mfr_number),
',name:',QUOTE(it.name),
'}') json
FROM sklad.items it
LEFT JOIN sklad.itemsMask im USING(item_id)
LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id
LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(it.accwareCatID,2),1)=ps.id
LEFT JOIN sklad.items it2 ON CONCAT(1,it.accwareCatID)=it2.accwareCatID
WHERE it.active='Y' AND im.closeout=TRUE LIMIT 0,15;

SELECT FOUND_ROWS();


SELECT 'Manufacturer','Condition',searchName1,searchName2,searchName3,searchName4,searchName5 FROM sklad.category WHERE category_id=530;

SELECT it.accwareCatID,it.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5
FROM sklad.items it
LEFT JOIN sklad.itemsMask im USING(item_id)
LEFT JOIN sklad.preference p USING(item_id)
LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id
WHERE it.active='Y' AND im.closeout=TRUE AND it.category_id=530;


SELECT CONCAT('{','id:',it.category_id,',p:',sc.parent,',n:',count(*),',nm:',QUOTE(sc.name),'}') jcat
FROM sklad.items it LEFT JOIN sklad.itemsMask im USING(item_id) LEFT JOIN sklad.category sc USING(category_id)
WHERE it.active='Y' AND im.closeout=TRUE
GROUP BY it.category_id;

SELECT it.category_id,sc.parent,count(*),QUOTE(sc.name)
FROM sklad.items it LEFT JOIN sklad.itemsMask im USING(item_id) LEFT JOIN sklad.category sc USING(category_id)
WHERE it.active='Y' AND im.closeout=TRUE
GROUP BY it.category_id;

********
SELECT sc.parent,sc.category_id,count(*),QUOTE(sc.name),gc.parent,gc.category_id,QUOTE(gc.name)

SELECT CONCAT('{','id:',it.category_id,',p:',sc.parent,',n:',count(*),',nm:',QUOTE(sc.name),',nmg:',QUOTE(gc.name),'}') jcat
FROM sklad.items it
LEFT JOIN sklad.itemsMask im USING(item_id)
LEFT JOIN sklad.category sc USING(category_id)
LEFT JOIN sklad.category gc ON sc.parent=gc.category_id
WHERE it.active='Y' AND gc.category_id IS NOT NULL AND im.vendor_id=2
GROUP BY it.category_id ORDER BY gc.name,sc.name;
*/
/*
          98 7654 3210
   0000 0000 0000 0000
                  ||||- (x01) special
                  |||-- (x02) closeout
                  ||--- (x04) bestprice
                  |---- (x08) commingsoon
*/
    final static String GET_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS " +
         "CONCAT('{'," +
          "'id:',it.item_id," +
          "IF(it2.item_id,CONCAT(',it:{id:',it2.item_id,',prc:',it2.sell_price,'}'),'')," +
          "',sku:',QUOTE(it.accwareCatID)," + // bnr=banner,sp=special,cl=clearance,bp=bestprice,cs=comesoon
          "IF(im.special|im.closeout|im.commingsoon|im.bestprice," +
            "CONCAT(',bnr:[{sp:',im.special,',cl:',im.closeout,',bp:',im.bestprice,',cs:',im.commingsoon,'}]'),'')," +
//          "CONCAT(',ban:',(im.special<<0x0) | (im.closeout<<0x1) | (im.bestprice<<0x2) | (im.commingsoon<<0x3))," +
          "',cnd:',QUOTE(cs.description)," +
          "',prc:',it.sell_price," +
          "',qty:',QUOTE(IF(it.stock='Y','Yes',it.stock_quantity))," +
          "',pkg:',QUOTE(ps.description)," +
          "',mnm:',QUOTE(it.mfr_name)," +
          "',mfr:',QUOTE(it.mfr_number)," +
          "',nm:',QUOTE(it.name)," +
         "'}') json " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id " +
        "LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(it.accwareCatID,2),1)=ps.id " +
        "LEFT JOIN sklad.items it2 ON CONCAT(1,it.accwareCatID)=it2.accwareCatID " +
        "WHERE it.active='Y' ";

    final static String GET_ITEMS_CATEGORY =
        "SELECT " +
         "CONCAT('{'," +
          "'id:',it.category_id," +
          "',p:',sc.parent," +
          "',n:',count(*)," +
          "',nm:',QUOTE(sc.name)," +
          "',nmg:',QUOTE(gc.name)," +
         "'}') jcat " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category sc USING(category_id) " +
        "LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
        "WHERE it.active='Y' AND gc.category_id IS NOT NULL ";

    final static String GET_ITEMS_PREFERENCE_HEAD =
        "SELECT 'Manufacturer','Condition',searchName1,searchName2,searchName3,searchName4,searchName5 FROM sklad.category WHERE category_id=?";

    final static String GET_ITEMS_PREFERENCE =
        "SELECT it.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id " +
        "WHERE it.active='Y' AND it.category_id=? ";

    public String getItemsPage(
        int catPage,        // what kind page (Clearance, Special, Search ...
        int pageNo,         // current page
        int itemsOnPage,    // how many items on page (15,25,50)
        int sortID,         // sorting see bellow
        int catID,          // category in item
        int lowPrice,       // search from low price
        int hiPrice,        // search until hight price
        String mfr,         // preference mfrName
        String cond,        // preference condition
        String prf1,        // preference 1 block
        String prf2,        // preference 2 block
        String prf3,        // preference 3 block
        String prf4,        // preference 4 block
        String prf5,        // preference 5 block
        String search,      // search inside block
        int needChangeCat   // ==1 need select category and preference
    ) {

        long startEvent = System.currentTimeMillis();

        //define what kind page (clearance,weekly spesial etc...)
        String selectedPage;
        switch(catPage) {
            case 1:  selectedPage = " AND im.closeout=TRUE "; break;
            case 2:  selectedPage = " AND im.vendor_id=2 "; break;
            case 3:  selectedPage = ""; break;
            case 11:  selectedPage = " AND im.closeout=TRUE "; break;
            default: selectedPage = " AND im.closeout=TRUE ";
        }

        // category
        String catstr = catID == 0 ? "" : " AND it.category_id=" + catID;

        // price range
        String prices = lowPrice != 0 && hiPrice != 0 ? " AND it.sell_price BETWEEN " + lowPrice + " AND " + hiPrice : "";

        // preference
        String prefs = "";
        prefs += mfr != null  ? " AND it.mfr_name='" + mfr + "' " : "";
        prefs += cond != null ? " AND cs.description='" + cond + "' " : "";
        prefs += prf1 != null ? " AND p.pref1='" + prf1 + "' " : "";
        prefs += prf2 != null ? " AND p.pref2='" + prf2 + "' " : "";
        prefs += prf3 != null ? " AND p.pref3='" + prf3 + "' " : "";
        prefs += prf4 != null ? " AND p.pref4='" + prf4 + "' " : "";
        prefs += prf5 != null ? " AND p.pref5='" + prf5 + "' " : "";

        // sort
        String sort;
        switch(sortID) {
            case 1: sort = " ORDER BY it.sell_price ASC "; break;
            case 2: sort = " ORDER BY it.sell_price DESC "; break;
            default: sort = "";
        }

        // search
        String query = "";
        if(search != null && search.length() != 0) {
            query = " AND MATCH(it.keywords) AGAINST('"+ search +"' IN BOOLEAN MODE) ";
        }

        StringBuilder items = new StringBuilder(400);
        StringBuilder cats = new StringBuilder(400);
        StringBuilder pref = new StringBuilder(400);
        int count = 0;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us;
            ResultSet rs;

            us = conn.prepareStatement(GET_ITEMS + selectedPage + catstr + prices + prefs + query + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * itemsOnPage);
            us.setInt(2, itemsOnPage);

            rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) items.append(',');
                items.append(rs.getString("json"));
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = us.executeQuery();
            if(rs.next()) {
                count = rs.getInt("cnt");
            }
            rs.close();
            us.close();

            if(catID == 0) { // subcategory
                us = conn.prepareStatement(GET_ITEMS_CATEGORY + selectedPage + query + " GROUP BY it.category_id ORDER BY gc.name,sc.name");
                rs = us.executeQuery();
                for(int idx = 0; rs.next(); idx++) {
                    if(idx != 0) cats.append(',');
                    cats.append(rs.getString("jcat"));
                }
                rs.close();
                us.close();
            }
            else { // preference
                if(needChangeCat ==  1) { // if select how many pages select pageNo no need select category and preference
                    Preferences mfrName = new Preferences(0);
                    Preferences condtn = new Preferences(1);
                    Preferences pref1 = new Preferences(2);
                    Preferences pref2 = new Preferences(3);
                    Preferences pref3 = new Preferences(4);
                    Preferences pref4 = new Preferences(5);
                    Preferences pref5 = new Preferences(6);

                    us = conn.prepareStatement(GET_ITEMS_PREFERENCE_HEAD);
                    us.setInt(1, catID);
                    rs = us.executeQuery();
                    if(rs.next()) {
                        mfrName.setTitle(rs.getString("Manufacturer"));
                        condtn.setTitle(rs.getString("Condition"));
                        pref1.setTitle(rs.getString("searchName1"));
                        pref2.setTitle(rs.getString("searchName2"));
                        pref3.setTitle(rs.getString("searchName3"));
                        pref4.setTitle(rs.getString("searchName4"));
                        pref5.setTitle(rs.getString("searchName5"));
                    }
                    rs.close();
                    us.close();

                    us = conn.prepareStatement(GET_ITEMS_PREFERENCE + selectedPage + prices + prefs + query);
                    us.setInt(1, catID);
                    rs = us.executeQuery();
                    while(rs.next()) {
                        mfrName.add(rs.getString("mfr_name"));
                        condtn.add(rs.getString("conds"));
                        pref1.add(rs.getString("pref1"));
                        pref2.add(rs.getString("pref2"));
                        pref3.add(rs.getString("pref3"));
                        pref4.add(rs.getString("pref4"));
                        pref5.add(rs.getString("pref5"));
                    }
                    rs.close();
                    us.close();

                    if(mfrName.haveData())  pref.append(mfrName.toString());
                    if(condtn.haveData())   pref.append(',').append(condtn.toString());
                    if(pref1.haveData())    pref.append(',').append(pref1.toString());
                    if(pref2.haveData())    pref.append(',').append(pref2.toString());
                    if(pref3.haveData())    pref.append(',').append(pref3.toString());
                    if(pref4.haveData())    pref.append(',').append(pref4.toString());
                    if(pref5.haveData())    pref.append(',').append(pref5.toString());
                }
            }// if(needChangeCat)
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItemsPage()", e);
        }

        return items.length() == 0 ? "" : "{its:[" + items.toString() +
                                          "],cnt:" + count +
                                          ",t:" + (int)(System.currentTimeMillis() - startEvent) +
                                          ",cts:[" + cats.toString() +
                                          "],prf:[" + pref.toString() +
                                          "]}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getClearanceItems()">
/*
explain
SELECT SQL_CALC_FOUND_ROWS CONCAT('{',
'id:',it.item_id,
IF(it2.item_id,CONCAT(',additem:[{id:',it2.item_id,',price:',it2.sell_price,'}]'),''),
',sku:',QUOTE(it.accwareCatID),
IF(im.special|im.closeout|im.commingsoon|im.bestprice,CONCAT(',banner:[{special:',im.special,',clearance:',im.closeout,',bestprice:',im.bestprice,',comesoon:',im.commingsoon,'}]'),''),
',cond:',QUOTE(cs.description),
',price:',it.sell_price,
',stock:',QUOTE(IF(it.stock='Y','Yes',it.stock_quantity)),
',packg:',QUOTE(ps.description),
',mfrName:',QUOTE(it.mfr_name),
',mfrNumber:',QUOTE(it.mfr_number),
',name:',QUOTE(it.name),
'}') json
FROM sklad.items it
LEFT JOIN sklad.itemsMask im USING(item_id)
LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id
LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(it.accwareCatID,2),1)=ps.id
LEFT JOIN sklad.items it2 ON CONCAT(1,it.accwareCatID)=it2.accwareCatID
WHERE it.active='Y' AND im.closeout=TRUE LIMIT 0,15;

SELECT FOUND_ROWS();


SELECT 'Manufacturer','Condition',searchName1,searchName2,searchName3,searchName4,searchName5 FROM sklad.category WHERE category_id=530;

SELECT it.accwareCatID,it.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5
FROM sklad.items it
LEFT JOIN sklad.itemsMask im USING(item_id)
LEFT JOIN sklad.preference p USING(item_id)
LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id
WHERE it.active='Y' AND im.closeout=TRUE AND it.category_id=530;


SELECT CONCAT('{','id:',it.category_id,',p:',sc.parent,',n:',count(*),',nm:',QUOTE(sc.name),'}') jcat
FROM sklad.items it LEFT JOIN sklad.itemsMask im USING(item_id) LEFT JOIN sklad.category sc USING(category_id)
WHERE it.active='Y' AND im.closeout=TRUE
GROUP BY it.category_id;

SELECT it.category_id,sc.parent,count(*),QUOTE(sc.name)
FROM sklad.items it LEFT JOIN sklad.itemsMask im USING(item_id) LEFT JOIN sklad.category sc USING(category_id)
WHERE it.active='Y' AND im.closeout=TRUE
GROUP BY it.category_id;

********
SELECT sc.parent,sc.category_id,count(*),QUOTE(sc.name),gc.parent,gc.category_id,QUOTE(gc.name)

SELECT CONCAT('{','id:',it.category_id,',p:',sc.parent,',n:',count(*),',nm:',QUOTE(sc.name),',nmg:',QUOTE(gc.name),'}') jcat
FROM sklad.items it
LEFT JOIN sklad.itemsMask im USING(item_id)
LEFT JOIN sklad.category sc USING(category_id)
LEFT JOIN sklad.category gc ON sc.parent=gc.category_id
WHERE it.active='Y' AND gc.category_id IS NOT NULL AND im.vendor_id=2
GROUP BY it.category_id ORDER BY gc.name,sc.name;
*/
/*
          98 7654 3210
   0000 0000 0000 0000
                  ||||- (x01) special
                  |||-- (x02) closeout
                  ||--- (x04) bestprice
                  |---- (x08) commingsoon
*/
    final static String GET_CLEARANCE_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS " +
         "CONCAT('{'," +
          "'id:',it.item_id," +
          "IF(it2.item_id,CONCAT(',it:{id:',it2.item_id,',prc:',it2.sell_price,'}'),'')," +
          "',sku:',QUOTE(it.accwareCatID)," + // bnr=banner,sp=special,cl=clearance,bp=bestprice,cs=comesoon
          "IF(im.special|im.closeout|im.commingsoon|im.bestprice," +
            "CONCAT(',bnr:[{sp:',im.special,',cl:',im.closeout,',bp:',im.bestprice,',cs:',im.commingsoon,'}]'),'')," +
//          "CONCAT(',ban:',(im.special<<0x0) | (im.closeout<<0x1) | (im.bestprice<<0x2) | (im.commingsoon<<0x3))," +
          "',cnd:',QUOTE(cs.description)," +
          "',prc:',it.sell_price," +
          "',qty:',QUOTE(IF(it.stock='Y','Yes',it.stock_quantity))," +
          "',pkg:',QUOTE(ps.description)," +
          "',mnm:',QUOTE(it.mfr_name)," +
          "',mfr:',QUOTE(it.mfr_number)," +
          "',nm:',QUOTE(it.name)," +
         "'}') json " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id " +
        "LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(it.accwareCatID,2),1)=ps.id " +
        "LEFT JOIN sklad.items it2 ON CONCAT(1,it.accwareCatID)=it2.accwareCatID " +
        "WHERE it.active='Y' ";

    final static String GET_CLEARANCE_ITEMS_CATEGORY =
        "SELECT " +
         "CONCAT('{'," +
          "'id:',it.category_id," +
          "',p:',sc.parent," +
          "',n:',count(*)," +
          "',nm:',QUOTE(sc.name)," +
          "',nmg:',QUOTE(gc.name)," +
         "'}') jcat " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category sc USING(category_id) " +
        "LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
        "WHERE it.active='Y' AND gc.category_id IS NOT NULL ";

    final static String GET_CLEARANCE_ITEMS_PREFERENCE_HEAD =
        "SELECT 'Manufacturer','Condition',searchName1,searchName2,searchName3,searchName4,searchName5 " +
        "FROM sklad.category " +
        "WHERE category_id=?";

    final static String GET_CLEARANCE_ITEMS_PREFERENCE =
        "SELECT it.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id " +
        "WHERE it.active='Y' AND it.category_id=? ";

    public String getClearanceItems(
        int catPage,        // what kind page (Clearance, Special, Search ...
        int pageNo,         // current page
        int itemsOnPage,    // how many items on page (15,25,50)
        int sortID,         // sorting see bellow
        int catID,          // category in item
        int lowPrice,       // search from low price
        int hiPrice,        // search until hight price
        String mfr,         // preference mfrName
        String cond,        // preference condition
        String prf1,        // preference 1 block
        String prf2,        // preference 2 block
        String prf3,        // preference 3 block
        String prf4,        // preference 4 block
        String prf5,        // preference 5 block
        String search,      // search inside block
        int needChangeCat   // ==1 need select category and preference
    ) {

        long startEvent = System.currentTimeMillis();

        //define what kind page (clearance,weekly spesial etc...)
        String selectedPage;
        switch(catPage) {
            case 1:  selectedPage = " AND im.closeout=TRUE "; break;
            case 2:  selectedPage = " AND im.vendor_id=2 "; break;
            case 3:  selectedPage = ""; break;
            default: selectedPage = " AND im.closeout=TRUE ";
        }

        // category
        String catstr = catID == 0 ? "" : " AND it.category_id=" + catID;

        // price range
        String prices = lowPrice != 0 && hiPrice != 0 ? " AND it.sell_price BETWEEN " + lowPrice + " AND " + hiPrice : "";

        // preference
        String prefs = "";
        prefs += mfr  != null ? " AND it.mfr_name='" + mfr + "' " : "";
        prefs += cond != null ? " AND cs.description='" + cond + "' " : "";
        prefs += prf1 != null ? " AND p.pref1='" + prf1 + "' " : "";
        prefs += prf2 != null ? " AND p.pref2='" + prf2 + "' " : "";
        prefs += prf3 != null ? " AND p.pref3='" + prf3 + "' " : "";
        prefs += prf4 != null ? " AND p.pref4='" + prf4 + "' " : "";
        prefs += prf5 != null ? " AND p.pref5='" + prf5 + "' " : "";

        // sort
        String sort = " ORDER BY it.vendor_id=" + VENDOR_CVS + " DESC,it.sell_price ASC ";
        switch(sortID) {
            case 1: sort = " ORDER BY it.sell_price ASC "; break;
            case 2: sort = " ORDER BY it.sell_price DESC "; break;
            case 3: sort = " ORDER BY it.vendor_id=" + VENDOR_CVS + " DESC,it.sell_price ASC "; break;
//            case 3: sort = " ORDER BY it.mfr_name ASC "; break;
//            case 4: sort = " ORDER BY it.mfr_name DESC "; break;
//            case 5: sort = " ORDER BY cs.description ASC "; break;
//            case 6: sort = " ORDER BY cs.description DESC "; break;
        }

        // search
        String query = "";
        if(search != null && search.length() != 0) {
            query = " AND MATCH(it.keywords) AGAINST('"+ search +"' IN BOOLEAN MODE) ";
        }

        StringBuilder items = new StringBuilder(400);
        StringBuilder cats = new StringBuilder(400);
        StringBuilder pref = new StringBuilder(400);
        int count = 0;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us;
            ResultSet rs;

            us = conn.prepareStatement(GET_CLEARANCE_ITEMS + selectedPage + catstr + prices + prefs + query + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * itemsOnPage);
            us.setInt(2, itemsOnPage);

            rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) items.append(',');
                items.append(rs.getString("json"));
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = us.executeQuery();
            if(rs.next()) {
                count = rs.getInt("cnt");
            }
            rs.close();
            us.close();

            if(catID == 0) { // subcategory
                us = conn.prepareStatement(GET_CLEARANCE_ITEMS_CATEGORY + selectedPage + query + " GROUP BY it.category_id ORDER BY gc.name,sc.name");
                rs = us.executeQuery();
                for(int idx = 0; rs.next(); idx++) {
                    if(idx != 0) cats.append(',');
                    cats.append(rs.getString("jcat"));
                }
                rs.close();
                us.close();
            }
            else { // preference
                if(needChangeCat ==  1) { // if select how many pages select pageNo no need select category and preference
                    Preferences mfrName = new Preferences(0);
                    Preferences condtn = new Preferences(1);
                    Preferences pref1 = new Preferences(2);
                    Preferences pref2 = new Preferences(3);
                    Preferences pref3 = new Preferences(4);
                    Preferences pref4 = new Preferences(5);
                    Preferences pref5 = new Preferences(6);

                    us = conn.prepareStatement(GET_CLEARANCE_ITEMS_PREFERENCE_HEAD);
                    us.setInt(1, catID);
                    rs = us.executeQuery();
                    if(rs.next()) {
                        mfrName.setTitle(rs.getString("Manufacturer"));
                        condtn.setTitle(rs.getString("Condition"));
                        pref1.setTitle(rs.getString("searchName1"));
                        pref2.setTitle(rs.getString("searchName2"));
                        pref3.setTitle(rs.getString("searchName3"));
                        pref4.setTitle(rs.getString("searchName4"));
                        pref5.setTitle(rs.getString("searchName5"));
                    }
                    rs.close();
                    us.close();

                    us = conn.prepareStatement(GET_CLEARANCE_ITEMS_PREFERENCE + selectedPage + prices + prefs + query);
                    us.setInt(1, catID);
                    rs = us.executeQuery();
                    while(rs.next()) {
                        mfrName.add(rs.getString("mfr_name"));
                        condtn.add(rs.getString("conds"));
                        pref1.add(rs.getString("pref1"));
                        pref2.add(rs.getString("pref2"));
                        pref3.add(rs.getString("pref3"));
                        pref4.add(rs.getString("pref4"));
                        pref5.add(rs.getString("pref5"));
                    }
                    rs.close();
                    us.close();

                    if(mfrName.haveData())  pref.append(mfrName.toString());
                    if(condtn.haveData())   pref.append(',').append(condtn.toString());
                    if(pref1.haveData())    pref.append(',').append(pref1.toString());
                    if(pref2.haveData())    pref.append(',').append(pref2.toString());
                    if(pref3.haveData())    pref.append(',').append(pref3.toString());
                    if(pref4.haveData())    pref.append(',').append(pref4.toString());
                    if(pref5.haveData())    pref.append(',').append(pref5.toString());
                }
            }// if(needChangeCat)
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getClearanceItems()", e);
        }

        return items.length() == 0 ? "" : "{its:[" + items.toString() +
                                          "],cnt:" + count +
                                          ",t:" + (int)(System.currentTimeMillis() - startEvent) +
                                          ",cts:[" + cats.toString() +
                                          "],prf:[" + pref.toString() +
                                          "]}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getBrandItems()">

    final static String GET_BRAND_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS " +
         "CONCAT('{'," +
          "'id:',it.item_id," +
          "IF(it2.item_id,CONCAT(',it:{id:',it2.item_id,',prc:',it2.sell_price,'}'),'')," +
          "',sku:',QUOTE(it.accwareCatID)," + // bnr=banner,sp=special,cl=clearance,bp=bestprice,cs=comesoon
          "IF(im.special|im.closeout|im.commingsoon|im.bestprice," +
            "CONCAT(',bnr:[{sp:',im.special,',cl:',im.closeout,',bp:',im.bestprice,',cs:',im.commingsoon,'}]'),'')," +
//          "CONCAT(',ban:',(im.special<<0x0) | (im.closeout<<0x1) | (im.bestprice<<0x2) | (im.commingsoon<<0x3))," +
          "',cnd:',QUOTE(cs.description)," +
          "',prc:',it.sell_price," +
          "',qty:',QUOTE(IF(it.stock='Y','Yes',it.stock_quantity))," +
          "',pkg:',QUOTE(ps.description)," +
          "',mnm:',QUOTE(it.mfr_name)," +
          "',mfr:',QUOTE(it.mfr_number)," +
          "',nm:',QUOTE(it.name)," +
         "'}') json " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id " +
        "LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(it.accwareCatID,2),1)=ps.id " +
        "LEFT JOIN sklad.items it2 ON CONCAT(1,it.accwareCatID)=it2.accwareCatID " +
        "WHERE it.active='Y' ";

    final static String GET_BRAND_ITEMS_CATEGORY =
        "SELECT " +
         "CONCAT('{'," +
          "'id:',it.category_id," +
          "',p:',sc.parent," +
          "',n:',count(*)," +
          "',nm:',QUOTE(sc.name)," +
          "',nmg:',QUOTE(gc.name)," +
         "'}') jcat " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category sc USING(category_id) " +
        "LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
        "WHERE it.active='Y' AND gc.category_id IS NOT NULL ";

    final static String GET_BRAND_ITEMS_PREFERENCE_HEAD =
        "SELECT 'Manufacturer','Condition',searchName1,searchName2,searchName3,searchName4,searchName5 FROM sklad.category WHERE category_id=?";

    final static String GET_BRAND_ITEMS_PREFERENCE =
        "SELECT it.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(it.accwareCatID,2),1)=cs.id " +
        "WHERE it.active='Y' AND it.category_id=? ";

    public String getBrandItems(
        int catPage,        // what kind page (Clearance, Special, Search ...
        int pageNo,         // current page
        int itemsOnPage,    // how many items on page (15,25,50)
        int sortID,         // sorting see bellow
        int catID,          // category in item
        int lowPrice,       // search from low price
        int hiPrice,        // search until hight price
        String mfr,         // preference mfrName
        String cond,        // preference condition
        String prf1,        // preference 1 block
        String prf2,        // preference 2 block
        String prf3,        // preference 3 block
        String prf4,        // preference 4 block
        String prf5,        // preference 5 block
        String search,      // search inside block
        String brandName,   // brand name
        int needChangeCat   // ==1 need select category and preference
    ) {

        long startEvent = System.currentTimeMillis();

        //define what kind page (clearance,weekly spesial etc...)
        String selectedPage;
        switch(catPage) {
            case 1:  selectedPage = " AND im.closeout=TRUE "; break;
            case 2:  selectedPage = " AND im.vendor_id=2 "; break;
            case 3:  selectedPage = ""; break;
            default: selectedPage = " AND im.closeout=TRUE ";
        }

        // category
        String catstr = catID == 0 ? "" : " AND it.category_id=" + catID;

        // price range
        String prices = lowPrice != 0 && hiPrice != 0 ? " AND it.sell_price BETWEEN " + lowPrice + " AND " + hiPrice : "";

        // preference
        String prefs = "";
        prefs += mfr != null  ? " AND it.mfr_name='" + mfr + "' " : "";
        prefs += cond != null ? " AND cs.description='" + cond + "' " : "";
        prefs += prf1 != null ? " AND p.pref1='" + prf1 + "' " : "";
        prefs += prf2 != null ? " AND p.pref2='" + prf2 + "' " : "";
        prefs += prf3 != null ? " AND p.pref3='" + prf3 + "' " : "";
        prefs += prf4 != null ? " AND p.pref4='" + prf4 + "' " : "";
        prefs += prf5 != null ? " AND p.pref5='" + prf5 + "' " : "";

        // sort
        String sort;
        switch(sortID) {
            case 1: sort = " ORDER BY it.sell_price ASC "; break;
            case 2: sort = " ORDER BY it.sell_price DESC "; break;
            default: sort = "";
        }

        // search
        String query = "";
        if(search != null && search.length() != 0) {
            query = " AND MATCH(it.keywords) AGAINST('"+ search +"' IN BOOLEAN MODE) ";
        }

        // brand
        String brand = "";
        if(brandName != null && brandName.length() != 0) {
            brand = " AND it.mfr_name='"+ brandName +"' ";
        }

        StringBuilder items = new StringBuilder(400);
        StringBuilder cats = new StringBuilder(400);
        StringBuilder pref = new StringBuilder(400);
        int count = 0;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us;
            ResultSet rs;

            us = conn.prepareStatement(GET_BRAND_ITEMS + selectedPage + catstr + prices + prefs + brand + query + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * itemsOnPage);
            us.setInt(2, itemsOnPage);

            rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) items.append(',');
                items.append(rs.getString("json"));
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = us.executeQuery();
            if(rs.next()) {
                count = rs.getInt("cnt");
            }
            rs.close();
            us.close();

            if(catID == 0) { // subcategory
                us = conn.prepareStatement(GET_BRAND_ITEMS_CATEGORY + selectedPage + brand + query + " GROUP BY it.category_id ORDER BY gc.name,sc.name");
                rs = us.executeQuery();
                for(int idx = 0; rs.next(); idx++) {
                    if(idx != 0) cats.append(',');
                    cats.append(rs.getString("jcat"));
                }
                rs.close();
                us.close();
            }
            else { // preference
                if(needChangeCat ==  1) { // if select how many pages select pageNo no need select category and preference
                    Preferences mfrName = new Preferences(0);
                    Preferences condtn = new Preferences(1);
                    Preferences pref1 = new Preferences(2);
                    Preferences pref2 = new Preferences(3);
                    Preferences pref3 = new Preferences(4);
                    Preferences pref4 = new Preferences(5);
                    Preferences pref5 = new Preferences(6);

                    us = conn.prepareStatement(GET_BRAND_ITEMS_PREFERENCE_HEAD);
                    us.setInt(1, catID);
                    rs = us.executeQuery();
                    if(rs.next()) {
                        mfrName.setTitle(rs.getString("Manufacturer"));
                        condtn.setTitle(rs.getString("Condition"));
                        pref1.setTitle(rs.getString("searchName1"));
                        pref2.setTitle(rs.getString("searchName2"));
                        pref3.setTitle(rs.getString("searchName3"));
                        pref4.setTitle(rs.getString("searchName4"));
                        pref5.setTitle(rs.getString("searchName5"));
                    }
                    rs.close();
                    us.close();

                    us = conn.prepareStatement(GET_BRAND_ITEMS_PREFERENCE + selectedPage + prices + prefs + brand + query);
                    us.setInt(1, catID);
                    rs = us.executeQuery();
                    while(rs.next()) {
                        mfrName.add(rs.getString("mfr_name"));
                        condtn.add(rs.getString("conds"));
                        pref1.add(rs.getString("pref1"));
                        pref2.add(rs.getString("pref2"));
                        pref3.add(rs.getString("pref3"));
                        pref4.add(rs.getString("pref4"));
                        pref5.add(rs.getString("pref5"));
                    }
                    rs.close();
                    us.close();

                    if(mfrName.haveData())  pref.append(mfrName.toString());
                    if(condtn.haveData())   pref.append(',').append(condtn.toString());
                    if(pref1.haveData())    pref.append(',').append(pref1.toString());
                    if(pref2.haveData())    pref.append(',').append(pref2.toString());
                    if(pref3.haveData())    pref.append(',').append(pref3.toString());
                    if(pref4.haveData())    pref.append(',').append(pref4.toString());
                    if(pref5.haveData())    pref.append(',').append(pref5.toString());
                }
            }// if(needChangeCat)

        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getBandItems()", e);
        }

        return items.length() == 0 ? "" : "{its:[" + items.toString() +
                                          "],cnt:" + count +
                                          ",t:" + (int)(System.currentTimeMillis() - startEvent) +
                                          ",cts:[" + cats.toString() +
                                          "],prf:[" + pref.toString() +
                                          "]}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getListOfBrand()">

    final static String GET_BRAND_LIST =
        "SELECT CONCAT('{nm:',QUOTE(LOWER(mfr_name)),',cn:',count(*),'}') json " +
        "FROM sklad.items " +
        "WHERE active='Y' " +
        "GROUP BY mfr_name";

    public String getListOfBrand() {

        long startEvent = System.currentTimeMillis();

        StringBuilder brands = new StringBuilder(400);

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(GET_BRAND_LIST);
            ResultSet rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) brands.append(',');
                brands.append(rs.getString("json"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getListOfBrand()", e);
        }

        return brands.length() == 0 ? "" : "[{barr:[" + brands.toString() +
                                           "],t:" + (int)(System.currentTimeMillis() - startEvent) +
                                           "}]";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsReseller()">
// Resellers

    public Items getItemsReseller(int categoryID, int subcatID, int sortID, Map<Integer,String> popData, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        String catstr = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0 ? "" : " AND c.category_id=" + subcatID  + " ";

        //sort
        String sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC";
        switch(sortID) {
            case  1 : sort = "i.sell_price ASC  "; break;
            case  2 : sort = "i.sell_price DESC "; break;
            case  3 : sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC"; break;
        }

        // Preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null && popData.size() > 0) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' "); break;
                }
            }
        }

        Preference mfrName = new Preference();
        Preference conds = new Preference();
        Preference sr1 = new Preference();
        Preference sr2 = new Preference();
        Preference sr3 = new Preference();
        Preference sr4 = new Preference();
        Preference sr5 = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us;
            ResultSet rs;

            // items count and fill preference
            if(subcatID != 0) {
                us = conn.prepareStatement(new StringBuilder(400).
                    append("SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 ").
                    append("FROM sklad.items i LEFT JOIN sklad.preference p USING(item_id) ").
                    append("LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id ").
                    append("WHERE i.active='Y' AND i.category_id=? ").append(strSel).
                    append(" AND i.res_price1<>0").toString());
                us.setInt(1, subcatID);

                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    sr1.addContent(rs.getString("pref1"));
                    sr2.addContent(rs.getString("pref2"));
                    sr3.addContent(rs.getString("pref3"));
                    sr4.addContent(rs.getString("pref4"));
                    sr5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }

            // make category
            us = conn.prepareStatement(new StringBuilder(500).
                append("SELECT count(*) itemsCount,g.category_id category_id,g.name name ").
                append("FROM sklad.items i LEFT JOIN sklad.category c USING(category_id) LEFT JOIN sklad.category g ON c.parent=g.category_id ").
                append("WHERE i.active='Y' AND i.res_price1<>0 AND g.category_id IS NOT NULL ").
                append("GROUP BY g.category_id ORDER BY g.sort,g.name").toString());

            rs = us.executeQuery();
            while(rs.next()) {
                items.addCategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
            }
            rs.close();
            us.close();

            // make subcategory
            if(categoryID != 0) {
                us = conn.prepareStatement(new StringBuilder(500).
                    append("SELECT count(*) itemsCount,c.category_id category_id,c.name name ").
                    append("FROM sklad.items i LEFT JOIN sklad.category c USING(category_id) ").
                    append("WHERE i.active='Y' AND i.res_price1<>0 AND c.parent=? ").
                    append("GROUP BY c.category_id ORDER BY c.sort,c.name").toString());
                us.setInt(1, categoryID);

                rs = us.executeQuery();
                while(rs.next()) {
                    items.addSubcategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
                }
                rs.close();
                us.close();
            }

            // items
            us = conn.prepareStatement(new StringBuilder(1800).
                append("SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.sell_price sellPrice,i.res_price1 resPrice1,").
                append("i.res_quantity2 resQuantity2,i.res_price2 resPrice2,i.res_quantity3 resQuantity3,i.res_price3 resPrice3,").
                append("i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock,").
                append("RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber,").

                append("c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5,").

                append("(im.special | im.closeout | im.commingsoon | im.bestprice) bannerMask,cs.description conditions ").

                append("FROM sklad.items i ").
                append("LEFT JOIN sklad.itemsMask im USING(item_id) ").
                append("LEFT JOIN sklad.category c USING(category_id) ").
                append("LEFT JOIN sklad.preference p ON i.item_id=p.item_id ").
                append("LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id ").
                append("WHERE i.active='Y' AND i.res_price1<>0 ").
                append(strSel).append(catstr).append(scatstr).
                append(" ORDER BY ").append(sort).append(" LIMIT ?,?").toString());
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);

            rs = us.executeQuery();
            for(int i = 0; rs.next(); i++) {
                if(subcatID != 0 && i == 0) {
                    mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                    conds.setTitle("Condition"); items.addPreference(conds);
                    sr1.setTitle(rs.getString("sn1")); items.addPreference(sr1);
                    sr2.setTitle(rs.getString("sn2")); items.addPreference(sr2);
                    sr3.setTitle(rs.getString("sn3")); items.addPreference(sr3);
                    sr4.setTitle(rs.getString("sn4")); items.addPreference(sr4);
                    sr5.setTitle(rs.getString("sn5")); items.addPreference(sr5);
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setCondition(rs.getString("conditions"));

                items.addItem(item);
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItemsReseler()", e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsSearch()">

    public Items getItemsSearch(int categoryID, int subcatID, String search, int sortID, Map<Integer,String> popData, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        if(search.length() < 3 && !search.contains("hp")) return items;

//        search = search.replaceAll("[^a-z0-9\\-\\+\\=\\#\\.]+", " ");
        search = search.replaceAll("([\\-]+)|([\\#]+)|([\\.]+)|([\\/]+)", "").trim(); // except

        if(search.length() == 0) return items;

        StringBuilder query = new StringBuilder();
        String[] split = search.split("\\p{Space}"); // parse search string
        if(split.length == 1) {
            query.append(search).append('*');
        }
        else {
            for(String word : split) {
                if("hp".equals(word)) word = "hpp";
                else if(word.length() < 3 || word.length() > 25) continue;

                query.append('+').append(word).append(' ');
            }
            if(query.length() == 0) return items;
        }

        //String titlestr = "0".equals(title) ? "" : ",i.xdesc";
        String catstr = categoryID == 0 ? "" : " AND c.parent=" + categoryID;
        String scatstr = subcatID == 0 ? "" : " AND c.category_id=" + subcatID;

        //sort
        String sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC";
        switch(sortID) {
            case  1 : sort = "i.sell_price ASC  "; break;
            case  2 : sort = "i.sell_price DESC "; break;
            case  3 : sort = "i.vendor_id=" + VENDOR_CVS + " DESC,i.sell_price ASC"; break;
        }

        // preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null && popData.size() > 0) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' "); break;
                }
            }
        }

        Preference mfrName = new Preference();
        Preference conds = new Preference();
        Preference sr1 = new Preference();
        Preference sr2 = new Preference();
        Preference sr3 = new Preference();
        Preference sr4 = new Preference();
        Preference sr5 = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us;
            ResultSet rs;

// <editor-fold defaultstate="collapsed" desc="get preference">
            // fill preference
            if(subcatID != 0) {
                us = conn.prepareStatement(new StringBuilder(400).
                    append("SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 ").
                    append("FROM sklad.items i LEFT JOIN sklad.preference p USING(item_id) ").
                    append("LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id ").
                    append("WHERE i.active='Y' AND i.category_id=? ").append(strSel).
                    append(" AND MATCH(i.keywords) AGAINST('").append(query).append("' IN BOOLEAN MODE) ").toString());
                us.setInt(1, subcatID);

                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    sr1.addContent(rs.getString("pref1"));
                    sr2.addContent(rs.getString("pref2"));
                    sr3.addContent(rs.getString("pref3"));
                    sr4.addContent(rs.getString("pref4"));
                    sr5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="get category & subcategory">
            // make category
            us = conn.prepareStatement(
                "SELECT COUNT(i.item_id) itemsCount,g.category_id category_id,g.name name " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.category c ON i.category_id=c.category_id " +
                "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
                "WHERE i.active='Y' AND g.category_id IS NOT NULL " +
                "AND MATCH(i.keywords) AGAINST('" + query + "' IN BOOLEAN MODE) " +
                "GROUP BY g.category_id ORDER BY g.sort,g.name");

            rs = us.executeQuery();
            while(rs.next()) {
                items.addCategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
            }
            rs.close();
            us.close();

            // make subcategory
            if(categoryID != 0) {
                us = conn.prepareStatement(
                    "SELECT COUNT(i.item_id) itemsCount,c.category_id category_id,c.name name " +
                    "FROM sklad.items i " +
                    "LEFT JOIN sklad.category c ON i.category_id=c.category_id " +
                    "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
                    "WHERE i.active='Y' AND g.category_id IS NOT NULL " + catstr +
                    " AND MATCH(i.keywords) AGAINST('" + query + "' IN BOOLEAN MODE) " +
                    "GROUP BY c.category_id ORDER BY c.sort,c.name");

                rs = us.executeQuery();
                while(rs.next()) {
                    items.addSubcategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
                }
                rs.close();
                us.close();
            }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="get items">
            // get items
            us = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.category_id categoryID,i.name itemName,i.sell_price sell_price," +
                "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3,i.res_quantity3 resQuantity3," +
                "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock,LEFT(RIGHT(i.accwareCatID,2),1) condid," + // 330006061-32 -> 3
                "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) banner_mask,cs.description conditions," +

                "IFNULL(i2.item_id,0) itemIDR,IFNULL(i2.stock_quantity-i2.reserv_quantity,0) quantityR," +
                "IF(i2.stock='Y',true,false) stockR,IFNULL(i2.sell_price,0) sellPriceR," +

                "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +

                "i.accwareCatID accwareCode,c.name categoryName,c.parent parent " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.itemsMask im USING(item_id) " +
                "LEFT JOIN sklad.preference p USING(item_id) " +
                "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
                "LEFT JOIN sklad.category c ON i.category_id=c.category_id " +
                "LEFT JOIN sklad.items i2 ON CONCAT(1,i.accwareCatID)=i2.accwareCatID " +
                "WHERE i.active='Y' " + strSel + catstr + scatstr +
                " AND MATCH(i.keywords) AGAINST('" + query.toString().trim() + "' IN BOOLEAN MODE) " +
                "GROUP BY i.item_id ORDER BY " + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);

            //System.out.println("AND MATCH(i.keywords) AGAINST('" + query.toString().trim() +"' IN BOOLEAN MODE)");
            rs = us.executeQuery();
            for(int i = 0; rs.next(); i++) {
                if(subcatID != 0 && i == 0) {
                    mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                    conds.setTitle("Condition"); items.addPreference(conds);
                    sr1.setTitle(rs.getString("sn1")); items.addPreference(sr1);
                    sr2.setTitle(rs.getString("sn2")); items.addPreference(sr2);
                    sr3.setTitle(rs.getString("sn3")); items.addPreference(sr3);
                    sr4.setTitle(rs.getString("sn4")); items.addPreference(sr4);
                    sr5.setTitle(rs.getString("sn5")); items.addPreference(sr5);
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setCategoryID(rs.getInt("categoryID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sell_price"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setConditionId(rs.getInt("condid"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setBannerMask(rs.getInt("banner_mask"));
                item.setCondition(rs.getString("conditions"));
                item.setCategoryName(rs.getString("categoryName"));
                item.setCategoryParent(rs.getInt("parent"));

                item.setItemIDR(rs.getInt("itemIDR"));
                item.setQuantityR(rs.getInt("quantityR"));
                item.setStockR(rs.getBoolean("stockR"));
                item.setPriceR(rs.getDouble("sellPriceR"));

                items.addItem(item);
            }
            rs.close();
            us.close();
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="get item count">
            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
//                System.out.println("### " + items.getItemsCount());
            }
            rs.close();
            us.close();
// </editor-fold>

        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItemsSearch()", e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="checkCustomer()">
// return -1: (customer present)   0: new customer

    public int checkCustomer(String email) {

        int result = 0;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT customer_id FROM sklad.customer WHERE email=LOWER(?)");
            us.setString(1, email);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                result = -1;
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "checkCustomer()", e);
        }

        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getCustomer()">
//mysql> select decode('4111111111111111','crypt'),encode(decode('4111111111111111','crypt'),'crypt');

    final static String GET_CUSTOMER =
        "SELECT customer_id,type,reseller_code,email,password,shippingAccount," +
        "shippingCompany,shippingFirstName,shippingLastName,shipping_address1,shipping_address2,shipping_city," +
        "shipping_state,shipping_country,shipping_zip,shipping_zip4,shippingPhone,shippingCell,shippingFax," +

        "billingCompany,billingFirstName,billingLastName,billing_address1,billing_address2,billing_city," +
        "billing_state,billing_country,billing_zip,billing_zip4,billingPhone,billingCell,billingFax," +

        "cc_name,ENCODE(cardNumber,'crypt') cardNumber,cc_number_add,left(cc_exp_date,2) month," +
        "right(cc_exp_date,2) year,bankPhone,notice,joined " +
        "FROM sklad.customer " +
        "WHERE email=LOWER(?) AND password=?";

    public Customer getCustomer(String email, String password) {

        Customer cust = new Customer();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT email FROM sklad.customer WHERE email=LOWER(?)");
            us.setString(1, email);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {

                PreparedStatement us2 = conn.prepareStatement(GET_CUSTOMER);
                us2.setString(1, email);
                us2.setString(2, password);

                ResultSet rs2 = us2.executeQuery();
                if(rs2.next()) { //email and password -> OK

                    cust.setCustomerID(rs2.getInt("customer_id"));
                    cust.setType(rs2.getString("type"));
                    cust.setResellerCode(rs2.getString("reseller_code"));
                    cust.setEmail(rs2.getString("email"));
                    cust.setPassword(rs2.getString("password"));
                    cust.setShippingAccount(rs2.getString("shippingAccount"));

                    cust.setShippingAddress(new Address(
                        rs2.getString("shippingCompany"),
                        rs2.getString("shippingFirstName"),
                        rs2.getString("shippingLastName"),
                        rs2.getString("shipping_address1"),
                        rs2.getString("shipping_address2"),
                        rs2.getString("shipping_city"),
                        rs2.getString("shipping_country"),
                        rs2.getString("shipping_state"),
                        rs2.getString("shipping_zip"),
                        rs2.getString("shipping_zip4"),
                        rs2.getString("shippingPhone"),
                        rs2.getString("shippingCell"),
                        rs2.getString("shippingFax")));

                    cust.setBillingAddress(new Address(
                        rs2.getString("billingCompany"),
                        rs2.getString("billingFirstName"),
                        rs2.getString("billingLastName"),
                        rs2.getString("billing_address1"),
                        rs2.getString("billing_address2"),
                        rs2.getString("billing_city"),
                        rs2.getString("billing_country"),
                        rs2.getString("billing_state"),
                        rs2.getString("billing_zip"),
                        rs2.getString("billing_zip4"),
                        rs2.getString("billingPhone"),
                        rs2.getString("billingCell"),
                        rs2.getString("billingFax")));

                    cust.setCName(rs2.getString("cc_name"));
                    cust.setCardNumber(rs2.getString("cardNumber"));
//                    cust.setCNumberAdd(rs2.getInt("cc_number_add"));
                    cust.setCNumberAdd(0);
                    cust.setCExpMonth(rs2.getString("month"));
                    cust.setCExpYears(rs2.getString("year"));
                    cust.setBankPhone(rs2.getString("bankPhone"));

                    cust.setNotice(rs2.getString("notice"));
                    cust.setJoined(rs2.getString("joined"));

                    rs2.close();
                    us2.close();
                }
                else { // email = OK passwd = NOT
                    cust.setCustomerID(0);
                }
            }
            else { // no users
                cust.setCustomerID(-1);
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getCustomer()", e);
        }

        return cust;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateCustomer()">
// if new make new or update customer

    public void updateCustomer(Customer cust, String ipAddress) {

        try(Connection conn = ds.getConnection()) {

            int pos = 1;
            CallableStatement cs = conn.prepareCall(
                "{ call sklad.updateCustomer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            cs.setString(pos++, cust.getEmail());
            cs.setString(pos++, cust.getPassword());
            cs.setString(pos++, cust.getType());
            cs.setString(pos++, ""); // resellerCode

            cs.setString(pos++, cust.getShipping().getCompany());
            cs.setString(pos++, cust.getShipping().getFirstName());
            cs.setString(pos++, cust.getShipping().getLastName());
            cs.setString(pos++, cust.getShipping().getAddress1());
            cs.setString(pos++, cust.getShipping().getAddress2());
            cs.setString(pos++, cust.getShipping().getCity());
            cs.setString(pos++, cust.getShipping().getState());
            cs.setString(pos++, cust.getShipping().getCountry());
            cs.setString(pos++, cust.getShipping().getZip());
            cs.setString(pos++, cust.getShipping().getZip4());
            cs.setString(pos++, cust.getShipping().getPhone());
            cs.setString(pos++, cust.getShipping().getCell());
            cs.setString(pos++, cust.getShipping().getFax());

            cs.setString(pos++, cust.getBilling().getCompany());
            cs.setString(pos++, cust.getBilling().getFirstName());
            cs.setString(pos++, cust.getBilling().getLastName());
            cs.setString(pos++, cust.getBilling().getAddress1());
            cs.setString(pos++, cust.getBilling().getAddress2());
            cs.setString(pos++, cust.getBilling().getCity());
            cs.setString(pos++, cust.getBilling().getState());
            cs.setString(pos++, cust.getBilling().getCountry());
            cs.setString(pos++, cust.getBilling().getZip());
            cs.setString(pos++, cust.getBilling().getZip4());
            cs.setString(pos++, cust.getBilling().getPhone());
            cs.setString(pos++, cust.getBilling().getCell());
            cs.setString(pos++, cust.getBilling().getFax());

            cs.setString(pos++, cust.getCName());
            cs.setString(pos++, cust.getCType());
            cs.setString(pos++, cust.getCardNumber());
            cs.setInt(pos++, cust.getCNumberAdd());
            cs.setString(pos++, cust.getCExpDate());
            cs.setString(pos++, cust.getBankPhone());

            cs.setString(pos++, cust.getNotice());
            cs.setString(pos++, ""); // orderNumber
            cs.setInt(pos++, 0); // count number
            cs.setDouble(pos++, 0d); // order total
            cs.setString(pos++, ipAddress);

            cs.setString(pos++, cust.getLicenseNumber());
            cs.setString(pos++, cust.getLicenseState());
            cs.setString(pos++, cust.getCheckNumber());
            cs.setString(pos++, cust.getCheckAccountNumber());
            cs.setString(pos++, cust.getCheckRoutingNumber());

            cs.registerOutParameter(pos, Types.INTEGER);

            cs.executeUpdate();

            cs.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "updateCustomer()", e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="newReseller()">

    public void newReseller(Customer cust, String ipAddress) {

        try(Connection conn = ds.getConnection()) {

            int pos = 1;
            CallableStatement cs = conn.prepareCall(
            "{ call sklad.updateCustomer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            cs.setString(pos++, cust.getEmail());
            cs.setString(pos++, cust.getPassword());
            cs.setString(pos++, cust.getType());
            cs.setString(pos++, cust.getResellerCode());

            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");

            cs.setString(pos++, cust.getBilling().getCompany());
            cs.setString(pos++, cust.getBilling().getFirstName());
            cs.setString(pos++, cust.getBilling().getLastName());
            cs.setString(pos++, cust.getBilling().getAddress1());
            cs.setString(pos++, cust.getBilling().getAddress2());
            cs.setString(pos++, cust.getBilling().getCity());
            cs.setString(pos++, cust.getBilling().getState());
            cs.setString(pos++, cust.getBilling().getCountry());
            cs.setString(pos++, cust.getBilling().getZip());
            cs.setString(pos++, cust.getBilling().getZip4());
            cs.setString(pos++, cust.getBilling().getPhone());
            cs.setString(pos++, cust.getBilling().getCell());
            cs.setString(pos++, cust.getBilling().getFax());

            cs.setString(pos++, "");
            cs.setString(pos++, "VISA");
            cs.setString(pos++, "");
            cs.setInt(pos++, 0);
            cs.setString(pos++, "");
            cs.setString(pos++, "");

            cs.setString(pos++, cust.getNotice());
            cs.setString(pos++, ""); // orderNumber
            cs.setInt(pos++, 0); // count number
            cs.setDouble(pos++, 0d); // order total
            cs.setString(pos++, ipAddress);

            cs.setString(pos++, cust.getLicenseNumber());
            cs.setString(pos++, cust.getLicenseState());
            cs.setString(pos++, cust.getCheckNumber());
            cs.setString(pos++, cust.getCheckAccountNumber());
            cs.setString(pos++, cust.getCheckRoutingNumber());

            cs.registerOutParameter(pos, Types.INTEGER);

            cs.executeUpdate();

            cs.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "newReseller()", e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getPassword()">
/*
 *
 * return: true  - send password by email
 *         false - customer not present
 *
*/
    public Customer getPassword(String email) {

        Customer cust = new Customer();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT customer_id,email,password,billingFirstName,billingLastName FROM sklad.customer WHERE email=LOWER(?)");
            us.setString(1, email);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {

                cust.setCustomerID(rs.getInt("customer_id"));
                cust.setEmail(rs.getString("email"));
                cust.setPassword(rs.getString("password"));

                Address billing = new Address();
                billing.setFirstName(rs.getString("billingFirstName"));
                billing.setLastName(rs.getString("billingLastName"));

                cust.setBillingAddress(billing);
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getPassword()", e);
        }

        return cust;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="copyToHistory()">

    public void copyToHistory(int orderID, String orderCopy) {

        if(orderCopy.length() == 0 || orderID == 0) return;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("INSERT LOW_PRIORITY sklad.orderHistory SET order_id:=?,order_cp:=?");
            us.setInt(1, orderID);
            us.setString(2, orderCopy);

            us.executeUpdate();

            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "copyToHistory()", e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeOrder()">

    final static String INSERT_ORDER =
        "INSERT sklad.orders SET order_date=CURRENT_TIMESTAMP(),orderNo=?,customer_id=?," +
        "blindShipping=?,shipping_price=?,shipping_code=?,shipping_sign=?,cod=?,paymentMethod=?," +
        "tax_price=?,amount=?,advert_id=?,notes=?,advertRefID=?,promoCodeID=?";
//        "tax_price=?,amount=?,advert_id=?,notes=?,cjpid=?,cjaid=?,cjsid=?,advertRefID=?,promoCodeID=?";

    final static String INSERT_ORDERLINE =
        "INSERT sklad.order_line SET order_id=?,item_id=?,quantity=?,sell_price=?,cost_price=?,vendor_id=?,warrantyDesc=?,warrantyPrice=?";

    final static String UPDATE_ITEMS_COUNT =
        "UPDATE sklad.items SET sell_quantity=sell_quantity+?,reserv_quantity=reserv_quantity+? WHERE item_id=?";

    public String makeOrder(Basket basket, AdvertShow advert) {

        if(basket.isEmpty()) return "";

        Customer cust = basket.getCustomer();
        // prefix like J O G add to OrderNumber
        String prefix = advert.getAdvertPrefix(basket.getCompuvestCookies().getAdvertSite());
        // if from facebook and have use order number show like xxx201202232428 - where xxx - user number
        String userName = basket.getCompuvestCookies().getUserName();
        if("FB".equals(prefix) && userName.length() != 0) {
            prefix = userName;
        }

        try(Connection conn = ds.getConnection()) {

            int promotionCodeID = 0;
            PreparedStatement us;

            CallableStatement cs = conn.prepareCall("{ call sklad.getOrderNumber() }");
            ResultSet rs = cs.executeQuery();
            if(rs.next()) {
                basket.setOrderNumber(prefix + rs.getString(1));
            }
            rs.close();
            cs.close();

            int pos = 1;
            cs = conn.prepareCall(
            "{ call sklad.updateCustomer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            cs.setString(pos++, cust.getEmail());
            cs.setString(pos++, cust.getPassword());
            cs.setString(pos++, cust.getType());
            cs.setString(pos++, cust.getResellerCode());

            cs.setString(pos++, cust.getShipping().getCompany());
            cs.setString(pos++, cust.getShipping().getFirstName());
            cs.setString(pos++, cust.getShipping().getLastName());
            cs.setString(pos++, cust.getShipping().getAddress1());
            cs.setString(pos++, cust.getShipping().getAddress2());
            cs.setString(pos++, cust.getShipping().getCity());
            cs.setString(pos++, cust.getShipping().getState());
            cs.setString(pos++, cust.getShipping().getCountry());
            cs.setString(pos++, cust.getShipping().getZip());
            cs.setString(pos++, cust.getShipping().getZip4());
            cs.setString(pos++, cust.getShipping().getPhone());
            cs.setString(pos++, cust.getShipping().getCell());
            cs.setString(pos++, cust.getShipping().getFax());

            cs.setString(pos++, cust.getBilling().getCompany());
            cs.setString(pos++, cust.getBilling().getFirstName());
            cs.setString(pos++, cust.getBilling().getLastName());
            cs.setString(pos++, cust.getBilling().getAddress1());
            cs.setString(pos++, cust.getBilling().getAddress2());
            cs.setString(pos++, cust.getBilling().getCity());
            cs.setString(pos++, cust.getBilling().getState());
            cs.setString(pos++, cust.getBilling().getCountry());
            cs.setString(pos++, cust.getBilling().getZip());
            cs.setString(pos++, cust.getBilling().getZip4());
            cs.setString(pos++, cust.getBilling().getPhone());
            cs.setString(pos++, cust.getBilling().getCell());
            cs.setString(pos++, cust.getBilling().getFax());

            cs.setString(pos++, cust.getCName());
            cs.setString(pos++, cust.getCType());
            cs.setString(pos++, cust.getCardNumber());
            cs.setInt(pos++, cust.getCNumberAdd());
            cs.setString(pos++, cust.getCExpDate());
            cs.setString(pos++, cust.getBankPhone());

            cs.setString(pos++, cust.getNotice());
            cs.setString(pos++, basket.getOrderNumber());
            cs.setInt(pos++, 1); // count number
            cs.setDouble(pos++, basket.getGrandTotal());
            cs.setString(pos++, basket.getCompuvestCookies().getIPAddress());

            cs.setString(pos++, cust.getLicenseNumber());
            cs.setString(pos++, cust.getLicenseState());
            cs.setString(pos++, cust.getCheckNumber());
            cs.setString(pos++, cust.getCheckAccountNumber());
            cs.setString(pos++, cust.getCheckRoutingNumber());

            cs.registerOutParameter(pos, Types.INTEGER);

            cs.executeUpdate();
            cust.setCustomerID(cs.getInt(pos));
            cs.close();

            // make coupon for next order
            // if customer use coupon no send more discount coupon
            pos = 1;
            if(basket.getPromotion() == null && basket.getCoupon() != null) {
                us = conn.prepareStatement("INSERT sklad.itemsPromotionCode SET item_id=0,procent=0,count=0,code=?,price=?,endDate=?");
                us.setString(pos++, basket.getCoupon().getCode());
                us.setDouble(pos++, basket.getCoupon().getPrice());
                us.setString(pos, new SimpleDateFormat("yyyy-MM-dd").format(basket.getCoupon().getValidDate()));
		us.executeUpdate();
		us.close();

		us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
		rs = us.executeQuery();
		if(rs.next()) {
                    promotionCodeID = rs.getInt(1);
		}
		rs.close();
		us.close();
            }
            // если в basket меньше чем сам купон - не запоминать ID for admin.order update
            else if(basket.getPromotion() != null && basket.getTotal(false) >= basket.getPromotion().getPromoPrice()) {
		promotionCodeID = basket.getPromotion().getPromoID();
            }

            pos = 1;
            us = conn.prepareStatement(INSERT_ORDER);
            us.setString(pos++, basket.getOrderNumber());
            us.setInt(pos++, cust.getCustomerID());
            us.setBoolean(pos++, basket.getBlindShipping());
            us.setDouble(pos++, basket.getShippingCost());
            us.setString(pos++, basket.getShippingCode());
            us.setBoolean(pos++, basket.getSignatureRequired());
            us.setString(pos++, basket.getPaymentMethod().equals("CD") ? "Y" : "N");
            us.setString(pos++, basket.getPaymentMethod());
            us.setDouble(pos++, basket.getTaxCost());
            us.setDouble(pos++, basket.getGrandTotal());
            us.setInt(pos++, advert.getAdvertID(basket.getCompuvestCookies().getAdvertSite()));
            us.setString(pos++, basket.getNotes());
            us.setString(pos++, basket.getCompuvestCookies().getAdvertValue());
            us.setInt(pos, promotionCodeID);
            us.executeUpdate();
            us.close();

            us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            rs = us.executeQuery();
            if(rs.next()) {
                basket.setOrderID(rs.getInt(1));
            }
            rs.close();
            us.close();

            us = conn.prepareStatement(INSERT_ORDERLINE);
            PreparedStatement us2 = conn.prepareStatement(UPDATE_ITEMS_COUNT);
            PreparedStatement us3 = conn.prepareStatement("UPDATE sklad.itemsPromotionCode SET count=count+1 WHERE item_id=? AND code=?");

            // stat items
            cs = conn.prepareCall("{ call sklad.itemTracking(?,?,?) }"); // categoryID, advertID, items_sum

            for(ItemsInBasket item : basket.getContents()) {

                us.setInt(1, basket.getOrderID());
                us.setInt(2, item.getItemID());
                us.setInt(3, item.getTotalQuantity());
                us.setDouble(4, item.getSellPrice());
                us.setDouble(5, item.getCostPrice());
                us.setInt(6, item.getVendorID());
                us.setString(7, item.getWarrantyDesc());
                us.setDouble(8, item.getWarrantyTotal());
                us.addBatch();

                us2.setInt(1, item.getTotalQuantity());
                us2.setInt(2, item.getTotalQuantity());
                us2.setInt(3, item.getItemID());
                us2.addBatch();

                if(item.getPromoProcent() != 0d) { // if present code
                    us3.setInt(1, item.getItemID());
                    us3.setString(2, item.getPromoCode()); // R0001
                    us3.addBatch();
                }

                cs.setInt(1, item.getCategoryID());
                cs.setInt(2, advert.getAdvertID(basket.getCompuvestCookies().getAdvertSite()));
                cs.setDouble(3, item.getSellPrice() * (double)item.getTotalQuantity());
                cs.addBatch();
            }
            us.executeBatch();
            us2.executeBatch();
            us3.executeBatch();
            cs.executeBatch();
            us.close();
            us2.close();
            us3.close();
            cs.close();

            // for statistic
            cs = conn.prepareCall("{ call sklad.setDomainStat(?,0,1,?) }"); // advertID, hitCount, orderCount, orderTotal
            cs.setInt(1, advert.getAdvertID(basket.getCompuvestCookies().getAdvertSite()));
            cs.setDouble(2, basket.getGrandTotal());
            cs.executeUpdate();
            cs.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "makeOrder()", e);
        }

        return basket.getOrderNumber();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeAmazonOrder()">

    public String makeAmazonOrder(Basket basket, String amazonOrderNumber, AdvertShow advert) {

        if(basket.isEmpty()) return "";

        // prefix like J O G add to OrderNumber
        String prefix = advert.getAdvertPrefix(basket.getCompuvestCookies().getAdvertSite());

        try(Connection conn = ds.getConnection()) {

            int customerID = 0;

            PreparedStatement us;

            CallableStatement cs = conn.prepareCall("{ call sklad.getOrderNumber() }");
            ResultSet rs = cs.executeQuery();
            if(rs.next()) {
                basket.setOrderNumber(prefix + "Z" + rs.getString(1)); // if present Z this mean amazon payment
            }
            rs.close();
            cs.close();

            int pos = 1;
            us = conn.prepareStatement(
                "INSERT LOW_PRIORITY sklad.customer SET joined=CURRENT_TIMESTAMP(),count_order=1,type='C',password='Change87654',email=?,last_order_no=?,sum_order=?");
            us.setString(pos++, amazonOrderNumber);
            us.setString(pos++, basket.getOrderNumber());
            us.setDouble(pos, basket.getGrandTotal());
            us.executeUpdate();
            us.close();

            us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            rs = us.executeQuery();
            if(rs.next()) {
                customerID = rs.getInt(1);
            }
            rs.close();
            us.close();

//            cs = conn.prepareCall("{ call sklad.setCustomerStat() }");
//            cs.close();

            // make coupon for next order
            // if customer use coupon no send more discount coupon
            int promotionCodeID = 0;
            //pos = 1;
/*            if(basket.getPromotion() == null && basket.getCoupon() != null) {
                us = conn.prepareStatement("INSERT sklad.itemsPromotionCode SET item_id=0,procent=0,count=0,code=?,price=?,endDate=?");
                us.setString(pos++, basket.getCoupon().getCode());
                us.setDouble(pos++, basket.getCoupon().getPrice());
                us.setString(pos, new SimpleDateFormat("yyyy-MM-dd").format(basket.getCoupon().getValidDate()));
		us.executeUpdate();
		us.close();

		us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
		rs = us.executeQuery();
		if(rs.next()) {
                    promotionCodeID = rs.getInt(1);
		}
		rs.close();
		us.close();
            }*/
            // если в basket меньше чем сам купон - не запоминать ID for admin.order update
            if(basket.getPromotion() != null && basket.getTotal(false) >= basket.getPromotion().getPromoPrice()) {
		promotionCodeID = basket.getPromotion().getPromoID();
            }

            pos = 1;
            us = conn.prepareStatement( // import=A - amazon
                "INSERT sklad.orders SET order_date=CURRENT_TIMESTAMP(),import='A',orderNo=?,amazonNo=?,customer_id=?," +
                "shipping_price=?,shipping_code=?,paymentMethod=?,tax_price=?,amount=?,advert_id=?,advertRefID=?,promoCodeID=?");
            us.setString(pos++, basket.getOrderNumber());
            us.setString(pos++, amazonOrderNumber);
            us.setInt(pos++, customerID);
            us.setDouble(pos++, basket.getShippingCost());
            us.setString(pos++, basket.getShippingCode());
            us.setString(pos++, basket.getPaymentMethod());
            us.setDouble(pos++, basket.getTaxCost());
            us.setDouble(pos++, basket.getGrandTotal());
            us.setInt(pos++, advert.getAdvertID(basket.getCompuvestCookies().getAdvertSite()));
            us.setString(pos++, basket.getCompuvestCookies().getAdvertValue());
            us.setInt(pos, promotionCodeID);
            us.executeUpdate();
            us.close();

            us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            rs = us.executeQuery();
            if(rs.next()) {
                basket.setOrderID(rs.getInt(1));
            }
            rs.close();
            us.close();

            us = conn.prepareStatement(INSERT_ORDERLINE);
            PreparedStatement us2 = conn.prepareStatement(UPDATE_ITEMS_COUNT);
            PreparedStatement us3 = conn.prepareStatement("UPDATE sklad.itemsPromotionCode SET count=count+1 WHERE item_id=? AND code=?");

            // stat items
            cs = conn.prepareCall("{ call sklad.itemTracking(?,?,?) }"); // categoryID, advertID, items_sum

            for(ItemsInBasket item : basket.getContents()) {

                us.setInt(1, basket.getOrderID());
                us.setInt(2, item.getItemID());
                us.setInt(3, item.getTotalQuantity());
                us.setDouble(4, item.getSellPrice());
                us.setDouble(5, item.getCostPrice());
                us.setInt(6, item.getVendorID());
                us.setString(7, item.getWarrantyDesc());
                us.setDouble(8, item.getWarrantyTotal());
                us.addBatch();

                us2.setInt(1, item.getTotalQuantity());
                us2.setInt(2, item.getTotalQuantity());
                us2.setInt(3, item.getItemID());
                us2.addBatch();

                if(item.getPromoProcent() != 0d) { // if present code
                    us3.setInt(1, item.getItemID());
                    us3.setString(2, item.getPromoCode()); // R0001
                    us3.addBatch();
                }

                cs.setInt(1, item.getCategoryID());
                cs.setInt(2, advert.getAdvertID(basket.getCompuvestCookies().getAdvertSite()));
                cs.setDouble(3, item.getSellPrice() * item.getTotalQuantity());
                cs.addBatch();
            }
            us.executeBatch();
            us2.executeBatch();
            us3.executeBatch();
            cs.executeBatch();
            us.close();
            us2.close();
            us3.close();
            cs.close();

            // for statistic
            cs = conn.prepareCall("{ call sklad.setDomainStat(?,0,1,?) }"); // advertID, hitCount, orderCount, orderTotal
            cs.setInt(1, advert.getAdvertID(basket.getCompuvestCookies().getAdvertSite()));
            cs.setDouble(2, basket.getGrandTotal());
            cs.executeUpdate();
            cs.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "makeAmazonOrder()", e);
        }

        return basket.getOrderNumber();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setOrderStatus()">

    public void setOrderStatus(String orderNo, String authStatus) {

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("UPDATE sklad.orders SET auth_status=? WHERE orderNo=?");
            us.setString(1, authStatus);
            us.setString(2, orderNo);
            us.executeUpdate();

            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "setOrderStatus()", e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setBasket()">

    public void setBasket(Basket basket, String sessionID) {

        StringBuilder itemids = new StringBuilder(200);
        for(ItemsInBasket item : basket.getContents()) {
            itemids.append(item.getItemID()).append(",");
        }
        itemids.deleteCharAt(itemids.length() - 1).toString(); // delete last ','

        String its = itemids.toString();
        its = its.length() > 100 ? its.substring(0,99) : its;
        sessionID = sessionID.length() > 19 ? sessionID.substring(0,19) : sessionID;

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("REPLACE LOW_PRIORITY sklad.basket_session SET sessionID=?,items_id=?");
            us.setString(1, sessionID);
            us.setString(2, its);
            us.executeUpdate();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "setBasket()", e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getBasket()">

    public Basket getBasket(String sessionID) {

        Basket basket = new Basket();

        try(Connection conn = ds.getConnection()) {

            String itemsid = "";

            PreparedStatement us = conn.prepareStatement("SELECT items_id FROM sklad.basket_session WHERE sessionID=?");
            us.setString(1, sessionID);
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                itemsid = rs.getString("items_id");
            }
            rs.close();
            us.close();

            if(itemsid.length() != 0) {
                us = conn.prepareStatement(
                    "SELECT item_id,name,sell_price,(stock_quantity-reserv_quantity) AS quantity," +
                    "weight,IF(stock='Y',true,false) stock,RIGHT(accwareCatID,12) accwareCode,mfr_number," +
                    "cs.description conditions,warrantyMask,warrantyPrice1,warrantyPrice2,warrantyPrice3 " +
                    "FROM sklad.items i " +
                    "LEFT JOIN conditions cs ON " +
                    "LEFT(RIGHT(accwareCatID,2),1)=cs.id " +
                    "WHERE i.active='Y' AND i.item_id IN(" + itemsid + ")");

                rs = us.executeQuery();
                while(rs.next()) {
                    Item item = new Item();

                    item.setItemID(rs.getInt("item_id"));
                    item.setItemName(rs.getString("name"));
                    item.setPrice(rs.getDouble("sell_price"));
                    item.setQuantity(rs.getInt("quantity"));
                    item.setWeight(rs.getDouble("weight"));
                    item.setStock(rs.getBoolean("stock"));
                    item.setAccwareCode(rs.getString("accwareCode"));
                    item.setMfrNumber(rs.getString("mfr_number"));
                    item.setCondition(rs.getString("conditions"));
                    item.setWarrantyMask(rs.getInt("warrantyMask"));
                    item.setWarrantyPrice1(rs.getDouble("warrantyPrice1"));
                    item.setWarrantyPrice2(rs.getDouble("warrantyPrice2"));
                    item.setWarrantyPrice3(rs.getDouble("warrantyPrice3"));

                    basket.addItem(new ItemsInBasket(item, false)); // (item,resseler)
                }
                rs.close();
                us.close();
            }
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getBasket()", e);
        }

        return basket;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getShippingChartUS()">
/*
 SELECT zip_from,zip_to,ground,3day,2day,2day_am,1day_saver,1day,1day_am FROM sklad.upszone WHERE LEFT('98055',3)>=zip_from AND LEFT('98055',3)<=zip_to;

 SELECT weight,z2,z302,z202,242,z132,z102,z102_am FROM sklad.upsrate WHERE weight=CEILING(1);

 SELECT uz.zip_from,uz.zip_to,uz.ground,ur.weight,ur.z2 FROM sklad.upszone uz,sklad.upsrate ur WHERE LEFT('98055',3)>=uz.zip_from AND LEFT('98055',3)<=uz.zip_to AND ur.weight=CEILING(1);

 SELECT uz.ground,ur.z2,
 ur.z2*(1-abs(sign(uz.ground))) as ground,
 ur.z302*(1-abs(sign(uz.3day))) as 3day
 FROM sklad.upszone uz,sklad.upsrate ur
 WHERE LEFT('98055',3)>=uz.zip_from AND LEFT('98055',3)<=uz.zip_to AND ur.weight=CEILING(1);

 SELECT zip_from,zip_to,ground,3day,2day,2day_am,1day_saver,1day,1day_am
 FROM sklad.upszone
 WHERE LEFT('98055',3)>=zip_from AND LEFT('98055',3)<=zip_to AND (

 );
*/
    public ArrayList<ShipRate> getShippingChartUS(String zipCode, double weight) {

        ArrayList<ShipRate> shipList = new ArrayList<>(7);

        try {
            Integer.parseInt(zipCode);
        }
        catch(NumberFormatException ignore) {
            return shipList;
        }

        // if weight > 150lbs all next lbs + $1.45
        double shipAddPrice = 0d;
        if(weight > 150d) {
            shipAddPrice = (weight - 150d) * 1.45;
            weight = 150d;
        }

        try(Connection conn = ds.getConnection()) {

            StringBuilder sql = new StringBuilder(200);
            ArrayList<ShipRate> dayList = new ArrayList<>();

            PreparedStatement us = conn.prepareStatement("SELECT ground,3day,2day,2day_am,1day_saver,1day,1day_am FROM sklad.upszone WHERE LEFT(?,3)>=zip_from AND LEFT(?,3)<=zip_to");
            us.setString(1, zipCode);
            us.setString(2, zipCode);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                if(rs.getString("ground") != null) {
                    sql.append(rs.getString("ground")).append(",");
                    dayList.add(new ShipRate("fdg", "Ground Shipping", rs.getString("ground")));

                    sql.append(rs.getString("ground")).append(",");
                    dayList.add(new ShipRate("usps", "USPS", rs.getString("ground")));
                }
                if(rs.getString("3day") != null) {
                    sql.append(rs.getString("3day")).append(",");
                    dayList.add(new ShipRate("fdx3d", "Three Day Air Shipping", rs.getString("3day")));
                }
                if(rs.getString("2day") != null) {
                    sql.append(rs.getString("2day")).append(",");
                    dayList.add(new ShipRate("fdx2d", "Second Day Air Shipping", rs.getString("2day")));
                }
//              if(rs.getString("2day_am") != null) {
//                  sql.append(rs.getString("2day_am")).append(",");
//                  dayList.add(new ShipRate("2day_am", "Fedex Second Day Air A.M.", rs.getString("2day_am")));
//              }
                if(rs.getString("1day_saver") != null) {
                    sql.append(rs.getString("1day_saver")).append(",");
                    dayList.add(new ShipRate("fdxso", "Next Day Air Saver", rs.getString("1day_saver")));
                }
                if(rs.getString("1day") != null) {
                    sql.append(rs.getString("1day")).append(",");
                    dayList.add(new ShipRate("fdxpo", "Next Day Air", rs.getString("1day")));
                }
                if(rs.getString("1day_am") != null) {
                    sql.append(rs.getString("1day_am")).append(",");
                    dayList.add(new ShipRate("fdxfo", "Next Day Air Early A.M.", rs.getString("1day_am")));
                }
            }
            rs.close();
            us.close();

            if(sql.length() != 0) {
                sql.deleteCharAt(sql.length() - 1); // delete ','

                us = conn.prepareStatement("SELECT " + sql.toString() + " FROM sklad.upsrate WHERE weight=CEILING(?)");
                us.setDouble(1, weight);

                rs = us.executeQuery();
                if(rs.next()) {
                    for(ShipRate ship : dayList) {
                        ship.setShipPrice(rs.getDouble(ship.getDay()), shipAddPrice);
                        shipList.add(ship);
                    }
                }
                rs.close();
                us.close();
            }

/*            if(dayList.size() < 7) { // if not all UPS add USPS
                us = conn.prepareStatement("SELECT z0 FROM sklad.uspsrate WHERE weight=CEILING(?)");
                us.setDouble(1, weight);

                rs = us.executeQuery();
                if(rs.next()) {
                    shipList.add(new ShipRate("usps", "USPS Express (3-8 Days)", rs.getDouble("z0")));
                }
                rs.close();
                us.close();
            }
*/
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getShippingChartUS()", e);
        }

        return shipList.isEmpty() ? getDefaultWeight() : shipList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getShippingChart()">

    public ArrayList<ShipRate> getShippingChart(String countryCode, double weight) {

        if(weight > 150d) {
            return getDefaultWeight();
        }

        ArrayList<ShipRate> shipList = new ArrayList<>(1);
        String zoneID = "";

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT zoneID FROM sklad.uspszone WHERE countryID=?");
            us.setString(1, countryCode);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                zoneID = rs.getString("zoneID");
            }
            rs.close();
            us.close();

            us = conn.prepareStatement("SELECT " + zoneID + " zone FROM sklad.uspsrate WHERE weight=CEILING(?)");
            us.setDouble(1, weight);

            rs = us.executeQuery();
            if(rs.next()) {
                shipList.add(new ShipRate("uspsx", "USPS International", rs.getDouble("zone")));
//                shipList.add(new ShipRate("fdx2i", "Fedex Express (2-3 Days)", rs.getDouble("zone") * 2.5)); // international UPS
//                shipList.add(new ShipRate("fdx2d", "Fedex 2-Day", rs.getDouble("zone") * 2.5)); // international UPS
                shipList.add(new ShipRate("fdx2d", "UPS 2nd day International", rs.getDouble("zone") * 2.5)); // international UPS
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getShippingChart()", e);
        }

        return shipList.isEmpty() ? getDefaultWeight() : shipList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getDefaultWeight()">

    private ArrayList<ShipRate> getDefaultWeight() {

        ArrayList<ShipRate> shipList = new ArrayList<>(1);

        shipList.add(new ShipRate("wen", "Weight Exceeded Normal", 0d));

        return shipList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="AdvertShow">

// <editor-fold defaultstate="collapsed" desc="getLeftMenuJSON()">
// make left menu

    public String getLeftMenuJSON() {

	StringBuilder menu = new StringBuilder(11000);

	try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='leftmenu'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		menu.append(rs.getString("content"));
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getMenuJSON()", e);
        }

        return menu.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getCountry">
// Country list

    public String getCountry() {

        StringBuilder country = new StringBuilder(6000);

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='country'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		country.append(rs.getString("content"));
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getCountry()", e);
        }

        return country.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAllState()">

    public String getAllState() {

        StringBuilder state = new StringBuilder(3000);

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='allstate'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		state.append(rs.getString("content"));
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getAllState()", e);
        }

        return state.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getUsaState()">

    public String getUsaState() {

        StringBuilder state = new StringBuilder(3000);

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='usastate'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		state.append(rs.getString("content"));
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getUsaState()", e);
        }

        return state.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getUsaStateMap()">

    protected Map getUsaStateMap() {

        Map usastaemap = new HashMap();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='usastatemap'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                ObjectInputStream ins = new ObjectInputStream(new ByteArrayInputStream(rs.getBytes("content")));

                usastaemap = (Map)ins.readObject();

                ins.close();
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getUsaStateMap()", e);
        }
        catch(Exception e) {
            log.log(Level.SEVERE, "getUsaStateMap()", e);
        }

        return usastaemap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getBrandPage()">
// Brand to string

    public String getBrandPage() {

        StringBuilder brand = new StringBuilder(115000);

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='brandpage'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		brand.append(rs.getString("content"));
            }
            rs.close();
            us.close();
        }
	catch(SQLException e) {
            log.log(Level.SEVERE, "getBrandPage()", e);
	}

        return brand.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAdvertTable()">

    final static String ADVERT_TABLE = "SELECT name,advert_id,prefix FROM sklad.advert";

    public Map<String,Advert> getAdvertTable() {

        Map<String,Advert> hashmap = new HashMap<>();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(ADVERT_TABLE);
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
		hashmap.put(rs.getString("name"),
                            new Advert(
                                rs.getInt("advert_id"),
                                rs.getString("name"),
                                rs.getString("prefix")
                                ));
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getAdvertTable()", e);
        }

        return hashmap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsCategoryPage()">
// advert every Category Items to Map

    protected Map getItemsCategoryPage() {

        Map hashmap = new HashMap();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='itemscategory'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                ObjectInputStream ins = new ObjectInputStream(new ByteArrayInputStream(rs.getBytes("content")));

                hashmap = (Map)ins.readObject();

                ins.close();
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getItemsCategoryPage()", e);
        }
        catch(Exception e) {
            log.log(Level.SEVERE, "getItemsCategoryPage()", e);
        }

        return hashmap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsSubcategoryPage()">
// advert every SubCategory Items to Map

    public Map getItemsSubcategoryPage() {

        Map hashmap = new HashMap();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='itemssubcat'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                ObjectInputStream ins = new ObjectInputStream(new ByteArrayInputStream(rs.getBytes("content")));

                hashmap =(Map)ins.readObject();

                ins.close();
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getItemsSubCategoryPage()", e);
        }
        catch(Exception e) {
            log.log(Level.SEVERE, "getItemsSubCategoryPage()", e);
        }

        return hashmap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getFirstPage()">
// advert first page and WeeklySpesial

    public Map getFirstPage() {

        Map hashmap = new HashMap();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='itemsfirstpage'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                ObjectInputStream ins = new ObjectInputStream(new ByteArrayInputStream(rs.getBytes("content")));

                hashmap =(Map)ins.readObject();

                ins.close();
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getItemsFirstPage()", e);
        }
        catch(Exception e) {
            log.log(Level.SEVERE, "getItemsFirstPage()", e);
        }

        return hashmap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsSearchPage()">
// advert Search page

    public String getItemsSearchPage() {

        StringBuilder json = new StringBuilder();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement("SELECT content FROM sklad.environment WHERE name='itemssearchpage'");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
		json.append(rs.getString("content"));
            }
            rs.close();
            us.close();
	}
	catch(SQLException e) {
            log.log(Level.SEVERE, "getItemsSearchPage()", e);
        }

	return json.toString();
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getInt()">

     protected int getInt(String value, int defInt) {

        int integer = defInt;
        try {
            integer = Integer.parseInt(value);
        }
        catch(Exception ignore) { }

        return integer;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="loadWebStats()">

    public WebStat loadWebStats() {

        WebStat webStat = new WebStat();
        webStat.setActive(0); // init active session

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(
                "SELECT count(*) todayAllCust " +
                "FROM sklad.customer " +
                "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' ','00:00:00') AND CONCAT(CURRENT_DATE(),' ','23:59:59')");
	    ResultSet rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setTodayAllCustomer(rs.getInt("todayAllCust"));
	    }
	    rs.close();
	    us.close();

            us = conn.prepareStatement(
                "SELECT count(*) cnt " +
                "FROM sklad.customer " +
                "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
                "AND billingFirstName='' AND type='C'");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setNotFillForm(rs.getInt("cnt")); // drop website but filled email and password not form
	    }
	    rs.close();
	    us.close();

	    us = conn.prepareStatement(
                "SELECT count(*) cnt " +
                "FROM sklad.customer " +
                "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
                "AND billingFirstName<>'' AND (last_order_no='' OR last_order_no='0') AND type='C'");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setYesFillForm(rs.getInt("cnt")); // drop website but filled billing address form
	    }
	    rs.close();
	    us.close();

	    us = conn.prepareStatement(
                "SELECT count(*) cnt " +
                "FROM sklad.customer " +
                "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
                "AND billingFirstName<>'' AND (last_order_no='' OR last_order_no='0') AND type='C' AND reseller_code<>''");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setReseller(rs.getInt("cnt")); // registered reseller
	    }
	    rs.close();
	    us.close();

            us = conn.prepareStatement("SELECT count(*) totalCust FROM sklad.customer");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setTotalCustomer(rs.getInt("totalCust"));
	    }
	    rs.close();
	    us.close();

	    us = conn.prepareStatement(
                "SELECT count(*) todayOrders " +
                "FROM sklad.orders " +
                "WHERE order_date BETWEEN CONCAT(CURRENT_DATE(),' ','00:00:00') AND CONCAT(CURRENT_DATE(),' ','23:59:59')");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setTodayOrders(rs.getInt("todayOrders"));
	    }
	    rs.close();
	    us.close();

	    us = conn.prepareStatement("SELECT count(*) totalOrders FROM sklad.orders");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setTotalOrders(rs.getInt("totalOrders"));
	    }
	    rs.close();
	    us.close();

	    us = conn.prepareStatement("SELECT SUM(hits) todayVisit FROM sklad.statDomain WHERE date=CURRENT_DATE() GROUP BY date");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setTodayVisit(rs.getInt("todayVisit"));
	    }
	    rs.close();
	    us.close();

	    us = conn.prepareStatement("SELECT SUM(hits) totalVisit FROM sklad.statDomain");
	    rs = us.executeQuery();
	    if(rs.next()) {
                webStat.setTotalVisit(rs.getInt("totalVisit"));
	    }
	    rs.close();
	    us.close();

	    us = conn.prepareStatement(
                "SELECT IFNULL(ad.name,'deleted') host,SUM(sd.hits) hits,SUM(sd.orders) orders,SUM(sd.amount) amount " +
                "FROM sklad.statDomain sd " +
                "LEFT JOIN sklad.advert ad USING(advert_id) " +
                "WHERE sd.date=CURRENT_DATE() " +
                "GROUP BY sd.advert_id");
	    rs = us.executeQuery();
	    while(rs.next()) {
                webStat.initAdvertSite(rs.getString("host"), rs.getInt("hits"), rs.getInt("orders"), rs.getDouble("amount"));
	    }
	    rs.close();
	    us.close();

        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "loadWebStatistics()", e);
        }

        return webStat;
    }
// </editor-fold>

}
